topic Re: Cross-table report with text and count sub-columns in ODS and Base Reporting

Cross-table report with text and count sub-columns

ChrisNZ — Tue, 08 Nov 2016 03:20:58 GMT

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;

Thank you!

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

Ksharp — Tue, 08 Nov 2016 04:56:41 GMT



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;

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

ChrisNZ — Tue, 08 Nov 2016 05:19:35 GMT

Nice! Thank you!

Any way to move the

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

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

Ksharp — Tue, 08 Nov 2016 07:22:34 GMT

Really uneasy. Can't you Data step to get it like MERGE and then PROC REPORT ?

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

ChrisNZ — Tue, 08 Nov 2016 20:52:15 GMT

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;

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

ChrisNZ — Tue, 08 Nov 2016 21:37:30 GMT

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;

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

Ksharp — Wed, 09 Nov 2016 04:40:34 GMT

Chris,
Well done. Never thought that before. Learn something new from you !