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.
PROC TRANSPOSE DATA=bloods2 OUT=bloodswide prefix=labvalue;
==> lots of error comes up in the log 'ERROR: The ID value "labvalue_Ery__Mean_Cell_Hemoglob" occurs twice in the same BY group.'
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;
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
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.