topic How do I transpose my data from long to wide? in SAS Procedures

How do I transpose my data from long to wide?

lavienrose1 — Wed, 18 Apr 2018 00:59:01 GMT

Hi,

I am having trouble transposing this long data set (see attachment) into a wide one.

I'd like for each row to correlate with a subject and for the blood results to go horizontally (wide) rather than vertically (long) as it stands presently.

Appreciate any help!

Attached excel spreadsheet is a sample of the data I am dealing with and shows how I would like the data transposed but unsure how to go about this with the PROC transpose command. I am using SAS 9.4.

E.G.

PROC TRANSPOSE DATA=bloods2 OUT=bloodswide prefix=labvalue;
BY SUBJECT;
ID labname;
VAR labvalue;
RUN ;

==> lots of error comes up in the log 'ERROR: The ID value "labvalue_Ery__Mean_Cell_Hemoglob" occurs twice in the same BY group.'

Many thanks

Re: How do I transpose my data from long to wide?

SuryaKiran — Wed, 18 Apr 2018 01:16:09 GMT

You need to sort the data by the by variables you have before transposing.

PROC SORT DATA=bloods2;

BY SUBJECT;

PROC TRANSPOSE DATA=bloods2 OUT=bloodswide prefix=labvalue;
BY SUBJECT;
ID labname;
VAR labvalue;
RUN ;

Re: How do I transpose my data from long to wide?

SuryaKiran — Wed, 18 Apr 2018 02:07:22 GMT

When you transpose, your labnames will be truncated or will throw an error because they will not meet the SAS Variable name rules. So use VALIDVARNAMES option.

options validvarname=v7;
PROC SORT DATA=bloods;
by subject sample_dt;
proc transpose data=bloods out=need prefix=Var_;
by subject sample_dt;
var labvalue;
label var_1="testing";
run;

If you want your names as it is in your reports then you can have them as labels.

proc sql;
create table dummy_var as 
select labname, CAT("var_",monotonic()) as dummy
from (select distinct labname from bloods);
quit;
proc sql;
select strip(dummy)||"='"||strip(labname)||"'" INTO: Label_List separated by " " 
from dummy_var;
quit;
proc sql;
create table have as 
select t1.*,t2.dummy
from bloods t1
left join dummy_var t2 on t1.labname=t2.labname;
quit;

PROC SORT DATA=have;
by subject sample_dt;
proc transpose data=have out=want(drop=_NAME_) ;
by subject sample_dt;
var labvalue;
ID dummy;
run;

DATA Want;
SET WANT;
LABEL &Label_List;
run;
proc print data=want label;
run;

Re: How do I transpose my data from long to wide?

lavienrose1 — Wed, 18 Apr 2018 03:18:37 GMT

Thanks very much SuryaKiran - that was extremely helpful