Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SAS Proc Report - Display group variable

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1445
Location: Azeroth

PostPosted: Sat Oct 27, 2012 7:41 pm    Post subject: SAS Proc Report - Display group variable
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    Post subject: Reply to: SAS Proc Report - Display group variable
Reply with quote

Hi vasanth,

Check if this helps. http://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: 1445
Location: Azeroth

PostPosted: Mon Oct 29, 2012 12:28 pm    Post subject:
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    Post subject: Reply to: SAS Proc Report - Display group variable
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: 1445
Location: Azeroth

PostPosted: Tue Oct 30, 2012 12:04 am    Post subject:
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: 1445
Location: Azeroth

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

However I was able to find a solution to the issue here http://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    Post subject: Reply to: SAS Proc Report - Display group variable
Reply with quote

Vasanthz,

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

http://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: 1013
Location: India

PostPosted: Thu Nov 15, 2012 2:40 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Detailed Transaction History report f... Kyle Carroll CICS 5 Tue Oct 18, 2016 12:42 am
No new posts abend sort based on count records in ... anatol DFSORT/ICETOOL 5 Mon Oct 17, 2016 10:10 pm
No new posts How to display value of an exit param... lind sh CICS 2 Sun Oct 09, 2016 12:28 pm
This topic is locked: you cannot edit posts or make replies. DSList (3.4) cannot display Chinese c... jacobdng TSO/ISPF 4 Wed Oct 05, 2016 2:51 pm
No new posts Get Record count in summary record fo... Atul Banke DFSORT/ICETOOL 21 Fri Sep 23, 2016 4:17 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us