The SAS Output Delivery System and reporting techniques

Cross-table report with text and count sub-columns

Accepted Solution Solved
Reply
PROC Star
Posts: 1,669
Accepted Solution

Cross-table report with text and count sub-columns

[ Edited ]

Hi all,

 

Can you think of a way to create this report from this data ?

 

 


data HAVE;
  input ROW COL VALUE;
cards;
R1 C1 x
R1 C2 y
R1 C1 x1
R1 C2 y
R2 C1 x
R2 C2 z
R2 C1 x
R2 C2 z
R2 C2 z
run;

 

aaa1.PNG

 

Thank you!


Accepted Solutions
Solution
‎11-08-2016 04:37 PM
Super User
Posts: 9,856

Re: Cross-table report with text and count sub-columns



data HAVE;
  input( ROW COL VALUE) ($);
cards;
R1 C1 x
R1 C2 y
R1 C1 x1
R1 C2 y
R2 C1 x
R2 C2 z
R2 C1 x
R2 C2 z
R2 C2 z
;
run;
proc freq data=have noprint;
table row*col*value/out=temp  list;
run;
proc report data=temp nowd ;
column row col,(value count);
define row/group;
define col/across ' ';
define value/display  ;
run;

View solution in original post


All Replies
Solution
‎11-08-2016 04:37 PM
Super User
Posts: 9,856

Re: Cross-table report with text and count sub-columns



data HAVE;
  input( ROW COL VALUE) ($);
cards;
R1 C1 x
R1 C2 y
R1 C1 x1
R1 C2 y
R2 C1 x
R2 C2 z
R2 C1 x
R2 C2 z
R2 C2 z
;
run;
proc freq data=have noprint;
table row*col*value/out=temp  list;
run;
proc report data=temp nowd ;
column row col,(value count);
define row/group;
define col/across ' ';
define value/display  ;
run;

PROC Star
Posts: 1,669

Re: Cross-table report with text and count sub-columns

Nice! Thank you!

Any way to move the 

 

y 2

 

on line up, together with the x rather than the x1 ?

 

This:

 

  C1 C2
ROW VALUE Frequency Count VALUE Frequency Count
R1 x 1  y 2
  x1 1   .
R2 x 2 z 3

 

instead of

 

  C1 C2
ROW VALUE Frequency Count VALUE Frequency Count
R1 x 1   .
  x1 1 y 2
R2 x 2 z 3

 

 

 

 

Super User
Posts: 9,856

Re: Cross-table report with text and count sub-columns

Really uneasy. Can't you Data step to get it like MERGE and then PROC REPORT ?
PROC Star
Posts: 1,669

Re: Cross-table report with text and count sub-columns

I tried this to put the lines in the right order in the data set, but the report is unchanged.

 

 

proc freq data=HAVE noprint;
  table ROW*COL*VALUE/out=FREQ  ;
run;

data SORTED;  
  set FREQ;
  by ROW COL;
  if first.COL then LINE=0;
  LINE+1;
run;

proc sort; 
  by ROW LINE COL;
run;

proc report data=SORTED nowd ;
  column ROW COL,(VALUE count);
  define ROW  /group;
  define COL  /across ' ';
  define VALUE/display  ;
  label count='#';
run;

 

 

 

PROC Star
Posts: 1,669

Re: Cross-table report with text and count sub-columns

Got it!

 


proc report data=SORTED nowd ;
  column ROW LINE COL,(VALUE count);
  define ROW   /group;
  define LINE  /group noprint;
  define COL   /across ' ';
  define VALUE /display  ;
  label count='#';
run;

 

Super User
Posts: 9,856

Re: Cross-table report with text and count sub-columns

Chris,
Well done. Never thought that before. Learn something new from you !
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 448 views
  • 1 like
  • 2 in conversation