topic Re: PROC REPORT - reporting a value "within" an across variable in SAS Procedures

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

TimH — Wed, 16 Apr 2014 13:16:02 GMT

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.

Section	Rank	date1	date2	date3
Receiving	1	Joe	Pete	Joe
	2	Sam	Sam	Pete
	3	Pete	Joe	Sam
(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.

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

data_null__ — Wed, 16 Apr 2014 13:33:43 GMT

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.

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;

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

Cynthia_sas — Wed, 16 Apr 2014 13:40:08 GMT

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

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

TimH — Wed, 16 Apr 2014 16:05:49 GMT

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?

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

Cynthia_sas — Wed, 16 Apr 2014 16:21:35 GMT

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