BookmarkSubscribeRSS Feed
MBI
Calcite | Level 5 MBI
Calcite | Level 5
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
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
You could try:

proc transpose data=student out=studtrans (drop=_name_);
by name;
var grade;
id exam;
run;
MBI
Calcite | Level 5 MBI
Calcite | Level 5
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.

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 1004 views
  • 0 likes
  • 3 in conversation