BookmarkSubscribeRSS Feed
flyingsohigh
Calcite | Level 5
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;
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
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]
flyingsohigh
Calcite | Level 5
Thanks. It works.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 1832 views
  • 0 likes
  • 2 in conversation