BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lavienrose1
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
SuryaKiran
Meteorite | Level 14

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

3 REPLIES 3
SuryaKiran
Meteorite | Level 14

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
SuryaKiran
Meteorite | Level 14

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
lavienrose1
Calcite | Level 5

Thanks very much SuryaKiran - that was extremely helpful

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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