Cross-table report with text and count sub-columns

[ChrisNZ]

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!

[Ksharp · Accepted solution]

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;

[ChrisNZ]

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

[Ksharp]

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

[ChrisNZ]

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;

[ChrisNZ]

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;

[Ksharp]

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