Help using Base SAS procedures

Turning a list of data into a 2D table?

Reply
Occasional Contributor MBI
Occasional Contributor
Posts: 19

Turning a list of data into a 2D table?

Hi All,

I've got a table that looks like this:
Name Exam Grade
Andy Q1 A
Andy Q2 B
Andy Q3 C
Bob Q1 .
Bob Q2 B
Bob Q3 B
Bob Q4 C
Chris Q1 D
Chris Q3 A


And I want to turn it into this:
Name Q1 Q2 Q3 Q4
Andy A B C .
Bob . B B C
Chris D . A .


I am currently doing this with an Array, a pair of nested do loops, and a Proc Report (DEFINE Name / GROUPSmiley Wink but this takes a bit of time. Is there a more efficient way of doing this?

Thanks,

MBI
SAS Super FREQ
Posts: 8,868

Re: Turning a list of data into a 2D table?

Hi:
One thing you could do is write your report directly from the DATA step program. I did NOT use ARRAYS in this program, just for ease of the example. But if you know how to use ARRAYS, then you can figure out how to change the program accordingly.

cynthia
[pre]
** first, make some data;
data student;
infile datalines;
input name $ exam $ grade $;
return;
datalines;
Andy Q1 A
Andy Q2 B
Andy Q3 C
Bob Q1 B
Bob Q2 B
Bob Q3 C
Chris Q1 D
Chris Q3 A
;
run;

** now, write the report using DATA _NULL_;
title 'Using DATA _NULL_';

ods listing;
ods html file='c:\temp\writerept.html' style=sasweb;

data _null_;
set student;
by name;
retain x1 x2 x3;
file print ods=(variables=(name x1 x2 x3));
if first.name then do;
x1 = '.';
x2 = '.';
x3 = '.';
end;
if exam = 'Q1' then x1 = grade;
else if exam = 'Q2' then x2 = grade;
else if exam = 'Q3' then x3 = grade;
if last.name then do;
put name x1 x2 x3;
end;
label x1 = 'Q1'
x2 = 'Q2'
x3 = 'Q3'
name = 'Student Name';
run;
ods html close;
[/pre]
N/A
Posts: 0

Re: Turning a list of data into a 2D table?

You could try:

proc transpose data=student out=studtrans (drop=_name_);
by name;
var grade;
id exam;
run;
Occasional Contributor MBI
Occasional Contributor
Posts: 19

Re: Turning a list of data into a 2D table?

Posted in reply to deleted_user
Thanks to both of you. That Proc Transpose is particularly efficient. What was taking me ~20 seconds to run now takes less than 2 seconds.
Ask a Question
Discussion stats
  • 3 replies
  • 169 views
  • 0 likes
  • 3 in conversation