topic Re: How to transpose many columns to one in SAS Programming

How to transpose many columns to one

raddad34 — Thu, 07 Oct 2021 17:14:31 GMT

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.

Re: How to transpose many columns to one

Reeza — Thu, 07 Oct 2021 17:32:30 GMT

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 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.

Re: How to transpose many columns to one

snoopy369 — Thu, 07 Oct 2021 17:20:47 GMT

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;

Re: How to transpose many columns to one

Reeza — Thu, 07 Oct 2021 17:35:47 GMT

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;

Re: How to transpose many columns to one

raddad34 — Thu, 07 Oct 2021 17:45:53 GMT

Thank you so much! this works perfectly!