## Turning a list of data into a 2D table?

Occasional Contributor
Posts: 19

# Turning a list of data into a 2D table?

Hi All,

I've got a table that looks like this:
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
SAS Super FREQ
Posts: 9,367

## 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]
Not applicable
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;
id exam;
run;
Occasional Contributor
Posts: 19