Solved
PROC Star
Posts: 2,329

# 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;``````

Thank you!

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

## 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;

```

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

## 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: 2,329

## 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: 10,761

## 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: 2,329

## 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: 2,329

## 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: 10,761

## 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
• 518 views
• 1 like
• 2 in conversation