IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

SAS Proc Report - Display group variable


IBM Mainframe Forums -> All Other Mainframe Topics
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Sat Oct 27, 2012 7:41 pm
Reply with quote

Hello,
There is a SAS dataset which looks like this,
Code:
                            Obs     DATE      MEASURE    STAT    INDICATOR

                             1     01APR00    WEIGHT       64        A
                             2     01APR00    WEIGHT       93        B
                             3     01APR00    WEIGHT       50        D
                             4     01APR00    HEIGHT      169        A
                             5     01APR00    HEIGHT      170        B
                             6     01APR01    WEIGHT       54        A
                             7     01APR01    HEIGHT      170        A

I have to process this dataset and produce an output which looks like this using PROC REPORT,
Expected output:
Code:
                                                      HEIGHT     WEIGHT
                                 DATE  INDICATOR        STAT       STAT
                              01APR00  A                 169         64
                              01APR00  B                 170         93
                              01APR00  D                   .         50
                              01APR01  A                 170         54

I tried the below code,
Code:
PROC REPORT DATA = WELLS NOWD;
  COLUMN DATE INDICATOR MEASURE,STAT;
  DEFINE DATE / FORMAT=DATE. GROUP;
  DEFINE INDICATOR / GROUP WIDTH=10;
  DEFINE MEASURE / ACROSS;
  DEFINE STAT / SUM;
QUIT;

Corresponding output:
Code:
                                                         MEASURE
                                                      HEIGHT     WEIGHT
                                 DATE  INDICATOR        STAT       STAT
                              01APR00  A                 169         64
                                       B                 170         93
                                       D                   .         50
                              01APR01  A                 170         54

But the output does not have DATE on each record of the output. Could you please let me know how to change the PROC REPORT so that the output will have DATE on each record.

The purpose to have date in each record is to do further Excel processing which requires date in each record.

Thanks & Regards,
Back to top
View user's profile Send private message
Suresh Kumar.N

New User


Joined: 16 Oct 2012
Posts: 7
Location: India

PostPosted: Mon Oct 29, 2012 8:19 am
Reply with quote

Hi vasanth,

Check if this helps. support.sas.com/documentation/cdl/en/procstat/63104/HTML/default/viewer.htm#procstat_freq_sect016.htm

I am not sure this will work or not, since we dont have sas in our shop. You can also try to do research on •
INCLUDEMISSINGCLASS=, I am eagerly waiting for a better solution for your post.[/url]
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Mon Oct 29, 2012 12:28 pm
Reply with quote

Hi Suresh,
Thanks for your time with this post. But the link is only pertaining to processing of missing values in calculations FREQ procedure.
The requirement is to display all occurances of a group variable in PROC REPORT(with an across variable).

I could use proc transpose + proc print, but it takes 2 steps and needs more CPU. I am sure PROC REPRORT could do it in single step, but having trouble taming it.

Thanks anyway,
Back to top
View user's profile Send private message
cpuhawg

Active User


Joined: 14 Jun 2006
Posts: 331
Location: Jacksonville, FL

PostPosted: Mon Oct 29, 2012 8:33 pm
Reply with quote

It is possible the GROUP parameter within the following code causes the date to be rolled up?

Code:

DEFINE DATE / FORMAT=DATE. GROUP;


Try:

Code:

DEFINE DATE / FORMAT=DATE.;
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Tue Oct 30, 2012 12:04 am
Reply with quote

1000th post at last.. Hope some of them were helpful.
my crush came and talked to me today and now 1000th post.. day could not have been any better :-)

Hi Pumba,
I tried removing the GROUP parameter, but when I remove it, PROC REPORT groups the data based on INDICATOR column and considers the date as numerics and sums it.

Output after removing GROUP parameter,
Code:
                                                         measure
                                                      HEIGHT     WEIGHT
                                 date  indicator        stat       stat
                              31DEC22  A                 339        118
                              01JUL40  B                 170         93
                              01APR00  D                   .         50


Thanks,
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India

PostPosted: Tue Oct 30, 2012 12:04 am
Reply with quote

However I was able to find a solution to the issue here www2.sas.com/proceedings/forum2007/242-2007.pdf page12.

This code,
Code:
PROC REPORT DATA = WELLS NOWD OUT=THINK;
  COLUMN DUMYDATE DATE INDICATOR MEASURE,STAT;
  DEFINE DUMYDATE / COMPUTED 'DATE';
  DEFINE DATE /  group NOPRINT;
  DEFINE INDICATOR / GROUP WIDTH=10;
  DEFINE MEASURE / ACROSS;
  DEFINE STAT / SUM;

COMPUTE BEFORE DATE;
  STORED_DATE=PUT(DATE,DATE.);
ENDCOMP;

COMPUTE DUMYDATE / CHARACTER ;
  DUMYDATE = STORED_DATE;
ENDCOMP;

QUIT;


produced the expected output :-)
Code:
                                                          measure
                                                       HEIGHT     WEIGHT
                             DATE       indicator        stat       stat
                             01APR00    A                 169         64
                             01APR00    B                 170         93
                             01APR00    D                   .         50
                             01APR01    A                 170         54


Also a Respected SAS expert PM'ed me
Quote:
THE ODS TAGSETS.RTF STATEMENT
As mentioned previously, the new SPANROWS option repeats the values for the group and order variables when
there is a continuation of the same value on the next page for all ODS destinations.

But was unable to make it work though, probably since my tagsets were outdated.

Thanks & Regards,
Back to top
View user's profile Send private message
Charles Wolters

New User


Joined: 30 Mar 2011
Posts: 48
Location: United States

PostPosted: Thu Nov 15, 2012 1:44 am
Reply with quote

Vasanthz,

Check out the following link which uses compute blocks to repeat the values of the group variable

www.sascommunity.org/wiki/Repeating_Group_variable_values_in_PROC_REPORT


Charles Wolters
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Thu Nov 15, 2012 2:40 pm
Reply with quote

vasanthz,

You can have this also as an option,
Code:
//STEP1    EXEC SAS9             
//INFILE   DD  *                 
 01APR00,WEIGHT,64,A             
 01APR00,WEIGHT,93,B             
 01APR00,WEIGHT,50,D             
 01APR00,HEIGHT,169,A             
 01APR00,HEIGHT,170,B             
 01APR01,WEIGHT,54,A             
 01APR01,HEIGHT,170,A             
//SYSIN      DD     *             
OPTIONS NOCENTER;                 
 DATA IP;                         
   INFILE INFILE DLM="," DSD;     
   INPUT                         
   DATE $                                                   
   MEASURE $                                               
   STAT                                                     
   INDICATOR $                                             
   ;                                                       
PROC PRINT DATA=IP;                                         
                                                           
PROC SQL;                                                   
  CREATE TABLE HTAB AS                                     
   SELECT DATE, INDICATOR,                                 
   MAX(CASE WHEN MEASURE = 'HEIGHT' THEN STAT END)AS HEIGHT,
   MAX(CASE WHEN MEASURE = 'WEIGHT' THEN STAT END)AS WEIGHT
   FROM IP                                                 
   GROUP BY DATE, INDICATOR                                 
   ;                                                       
QUIT;                                                       
PROC PRINT DATA=HTAB;                                       


Thanks,
sushanth
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> All Other Mainframe Topics

 


Similar Topics
Topic Forum Replies
No new posts Extracting Variable decimal numbers f... DFSORT/ICETOOL 17
No new posts Need help on formatting a report DFSORT/ICETOOL 14
No new posts Variable Output file name DFSORT/ICETOOL 8
No new posts Creating Report using SORT DFSORT/ICETOOL 7
No new posts Moving Or setting POINTER to another ... COBOL Programming 2
Search our Forums:

Back to Top