The SAS Output Delivery System and reporting techniques

PROC REPORT to create mulitple-dimension tables

Reply
Occasional Contributor
Posts: 14

PROC REPORT to create mulitple-dimension tables

I am trying to use PROC REPORT to create tables like:
Variables in row:
Name Age Sex School Grade
Variables in column:
Year across 06 - 08, within each year, Score Math: Score Read, Rank, etc.

My problem is that, I cannot have a row displayed for a kid if the kid have data in more than 1 years. It's always displayed in multiple rows.

Does someone know the reason?

My code:
PROC REPORT DATA=set1 NOWD list ls=100;
COL (Name Age Sex Grade year,
(Score_Math Score_Read Rank);
DEFINE Name / Display ;
DEFINE Age / Display ;
DEFINE Sex / Display ;
DEFINE Grade/Group;
DEFINE Year / 'Year' descending Across;
break after grade / skip;
DEFINE Score_Math / min;
DEFINE Score_Read / min;
DEFINE Rank / min;
run;
quit;
SAS Super FREQ
Posts: 8,742

Re: PROC REPORT to create mulitple-dimension tables

Hi:
If you want the rows to "collapse" ... so that you only have 1 row per child, then you cannot use DISPLAY as the usage for the NAME. In fact, PROC REPORT is probably telling you that it can't group or collapse the data with this note in the SAS log:
[pre]
NOTE: Groups are not created because the usage of Name is DISPLAY. To avoid this note, change all GROUP variables to ORDER variables.
[/pre]

In addition to what is mentioned in the note (to change all GROUP variables to ORDER variables), the other workaround is to change the usage of the DISPLAY variables to either GROUP (to get collapsing down to 1 row per child) or to ORDER (no collapsing -- will have multiple rows per child). In addition the left-to-right order of the variables on the column statement might impact the overall look and feel of the report.

Consider the program below and compare all the different outputs when the usage is DISPLAY for NAME, SEX, AGE) versus when the usage is ORDER or GROUP.

cynthia
[pre]
data set1;
set sashelp.class;
if age ge 16 then grade =12;
else if age in (14,15) then grade = 11;
else if age in (12,13) then grade = 10;
else if age in (11) then grade = 9;
do year = 2006 to 2008 by 1;
score_math = age + height + weight / 100;
score_read = height + weight /100;
rank = _n_;
output;
end;
run;

ods listing close;
ods html file='c:\temp\orig.html' style=sasweb;
PROC REPORT DATA=set1 NOWD list;
COL Name Age Sex Grade year,(Score_Math Score_Read Rank);
title '1) Your Original Program';
DEFINE Name / Display ;
DEFINE Age / Display ;
DEFINE Sex / Display ;
DEFINE Grade/Group;
DEFINE Year / 'Year' descending Across;
DEFINE Score_Math / min;
DEFINE Score_Read / min;
DEFINE Rank / min;
break after grade / skip;
compute after grade;
line ' ';
endcomp;
run;
ods html close;

ods html file='c:\temp\examp2ab.html' style=sasweb;
PROC REPORT DATA=set1 NOWD list;
COL Name Age Sex Grade year,(Score_Math Score_Read Rank);
title '2a) Vars changed to Order';
DEFINE Name / Order ;
DEFINE Age / Order ;
DEFINE Sex / Order ;
DEFINE Grade/Group;
DEFINE Year / 'Year' descending Across;
DEFINE Score_Math / min;
DEFINE Score_Read / min;
DEFINE Rank / min;
break after grade / skip;
compute after grade;
line ' ';
endcomp;
run;

PROC REPORT DATA=set1 NOWD list;
COL Name Age Sex Grade year,(Score_Math Score_Read Rank);
title '2b) Vars changed to Group';
DEFINE Name / Group ;
DEFINE Age / Group ;
DEFINE Sex / Group ;
DEFINE Grade/Group;
DEFINE Year / 'Year' descending Across;
DEFINE Score_Math / min;
DEFINE Score_Read / min;
DEFINE Rank / min;
break after grade / skip;
compute after grade;
line ' ';
endcomp;
run;
ods html close;

ods html file='c:\temp\examp3.html' style=sasweb;
PROC REPORT DATA=set1 NOWD list;
COL Grade Sex Age Name year,(Score_Math Score_Read Rank);
title '3) With Different Var Order and Usage';
DEFINE Grade/Group;
DEFINE Sex / Group ;
DEFINE Age / Group ;
DEFINE Name / Group ;
DEFINE Year / 'Year' descending Across;
DEFINE Score_Math / min;
DEFINE Score_Read / min;
DEFINE Rank / min;
break after grade / skip;
compute after grade;
line ' ';
endcomp;
run;
ods html close;
[/pre]
Occasional Contributor
Posts: 14

Re: PROC REPORT to create mulitple-dimension tables

Thanks. It works.
Ask a Question
Discussion stats
  • 2 replies
  • 993 views
  • 0 likes
  • 2 in conversation