Hello, I have a dataset I'm trying to reshape using proc transpose but I cant quite seem to figure it out
data have;
input id $ record1 $ record2 $ record3 $;
datalines;
A1 a15 n14
A1 a15 b12 b17
A1 a12 b18
A2 a29 a28 a21
A3 a34 a36 c34
;
I want this data to look like this:
data want;
input id $ record $;
A1 a15
A1 n14
A1 a15
A1 b12
A1 b17
A1 a12
A1 b18
A2 a29
A2 a28
A2 a21
A3 a34
A3 a36
A3 c34
Using proc transpose i get a set with multiple columns and i cant figure out how to get only one column. Does anyone have any tips on how to do this? Thank you.
You should just do this in the data step - easier and faster than coercing PROC TRANSPOSE to do what you want! Just make an array of your variables and then `output` new rows when you have a value.
data want;
set have;
array recs record1-record3;
do _i = 1 to dim(recs);
if not missing(recs[_i]) then do;
record = recs[_i];
output;
end;
end;
keep record id;
run;
proc transpose data=have out=want prefix=record; by ID; var record1-record3; run;
EDIT: In this case I agree with @snoopy369 a data step is easier with the duplicate entries, otherwise you need to add a grouping variable.
Transposing data tutorials:
Long to Wide:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-long-to-wide-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-long-to-wide-using-the-data-step/
Wide to Long:
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
And sometimes a double transpose is needed for extra wide data sets:
https://gist.github.com/statgeek/2321b6f62ab78d5bf2b0a5a8626bd7cd
@raddad34 wrote:
Hello, I have a dataset I'm trying to reshape using proc transpose but I cant quite seem to figure it out
data have; input id $ record1 $ record2 $ record3 $; datalines; A1 a15 n14 A1 a15 b12 b17 A1 a12 b18 A2 a29 a28 a21 A3 a34 a36 c34
;I want this data to look like this:
data want; input id $ record $;
A1 a15 A1 n14 A1 a15 A1 b12 A1 b17 A1 a12 A1 b18 A2 a29 A2 a28 A2 a21 A3 a34 A3 a36 A3 c34Using proc transpose i get a set with multiple columns and i cant figure out how to get only one column. Does anyone have any tips on how to do this? Thank you.
If you did want to use PROC TRANSPOSE I think you need to add a variable because your ID is repeated.
data temp;
set have;
by id;
if first.ID then counter=1;
else counter+1;
run;
proc transpose data=temp out=want prefix=record;
by ID counter;
var record1 record2 record3;
run;
You should just do this in the data step - easier and faster than coercing PROC TRANSPOSE to do what you want! Just make an array of your variables and then `output` new rows when you have a value.
data want;
set have;
array recs record1-record3;
do _i = 1 to dim(recs);
if not missing(recs[_i]) then do;
record = recs[_i];
output;
end;
end;
keep record id;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.