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
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 ;
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 ;
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 very much SuryaKiran - that was extremely helpful
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.