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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.