Help using Base SAS procedures

PROC REPORT - reporting a value "within" an across variable

Reply
Contributor
Posts: 21

PROC REPORT - reporting a value "within" an across variable

I have a set of observations with date, section, rank, and identifier.   I want to have a report with a row for each rank, a column for each date, and the identifier as the value in the column.

SectionRankdate1date2date3
Receiving1JoePeteJoe
2SamSamPete
3PeteJoeSam
(next section)

How do I do this with PROC REPORT?  What I've tried so far gives me a collection of rows with a 1 or a ',' in each column and the identifiers on the right side.

I defined Rank as an ORDER variable (tried GROUP) too.  DATE as an ACROSS variable.  I've tried the identifier as COMPUTED and as just a column.  I tried it as an ANALYSIS variable but it's not numeric.

Respected Advisor
Posts: 3,799

Re: PROC REPORT - reporting a value "within" an across variable

You have to use a little trick but I can't think of the explanation of how it works right now.  Perhaps will have the relevant facts to share.

4-16-2014 8-31-51 AM.png

data across;
   input section $ @;
   do rank=1 to 3;
     
do date=1 to 3;
        
input id $ @;
         output;
        
end;
     
end;
  
cards;
Receiving
Joe   Pete  Joe
Sam   Sam   Pete
Pete  Joe   Sam
Shipping
Joe   Pete  Joe
Sam   Sam   Pete
Pete  Joe   Sam
;;;;
   run;
proc print;
  
run;
proc report nowd list;
  
columns section rank date, id dummy;
   define section / group;
  
define rank / group;
  
define date / across;
  
define id / display ' ';
  
define dummy / noprint;
  
run;
SAS Super FREQ
Posts: 8,865

Re: PROC REPORT - reporting a value "within" an across variable

Posted in reply to data_null__


Hi:

  Yes, I have an explanation of why you need the "trick" on pages 4, 5 and 6 of this paper: http://support.sas.com/resources/papers/proceedings14/SAS388-2014.pdf -- basically, PROC REPORT needs a numeric variable to summarize in order to collapse the rows with the GROUP usage. So there has to be a numeric variable or a statistic like N (even if it's NOPRINT) in order for the character values to be placed underneath the ACROSS items.

cynthia

Contributor
Posts: 21

Re: PROC REPORT - reporting a value "within" an across variable

Posted in reply to Cynthia_sas

That worked well.  Now a follow-on question - If I added a variable for what they were ranked by (say sales), could I show ID and sales at the intersection of each date and rank?

SAS Super FREQ
Posts: 8,865

Re: PROC REPORT - reporting a value "within" an across variable

Hi:

  You would need to change the syntax of your COLUMN statement. The PROC REPORT documentation shows how to use the comma operator with parentheses to group items under an ACROSS usage variable. Showing the code you've tried and a sample of your data would be very helpful. In addition, knowing your final ODS destination of choice (part of your code) would be helpful too.

cynthia

Ask a Question
Discussion stats
  • 4 replies
  • 536 views
  • 0 likes
  • 3 in conversation