Help using Base SAS procedures

How do I transpose my data from long to wide?

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

How do I transpose my data from long to wide?

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


Accepted Solutions
Solution
‎04-17-2018 11:18 PM
Valued Guide
Posts: 590

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

Posted in reply to lavienrose1

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 ;

Thanks,
Suryakiran

View solution in original post


All Replies
Solution
‎04-17-2018 11:18 PM
Valued Guide
Posts: 590

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

Posted in reply to lavienrose1

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 ;

Thanks,
Suryakiran
Valued Guide
Posts: 590

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

Posted in reply to lavienrose1

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;
Thanks,
Suryakiran
Occasional Contributor
Posts: 17

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

Posted in reply to SuryaKiran

Thanks very much SuryaKiran - that was extremely helpful

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 190 views
  • 0 likes
  • 2 in conversation