topic Re: Turning a list of data into a 2D table? in SAS Procedures

Turning a list of data into a 2D table?

MBI — Mon, 10 Mar 2008 15:19:18 GMT

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 / GROUP;) but this takes a bit of time. Is there a more efficient way of doing this?

Thanks,

MBI

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

Cynthia_sas — Mon, 10 Mar 2008 22:07:29 GMT

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]

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

deleted_user — Tue, 11 Mar 2008 07:43:52 GMT

You could try:

proc transpose data=student out=studtrans (drop=_name_);
by name;
var grade;
id exam;
run;

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

MBI — Tue, 11 Mar 2008 17:21:14 GMT

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.