Hi everyone, I am very new at SAS and am trying to transpose a set of data but having issues.
What I have:
UUID | Vent_day_0 | Vent_day_1 | Vent_day_2 | Vent_day_3 |
UU0300 | 0 | 1 | 1 | 0 |
UU0301 | 0 | 0 | 1 | 1 |
UU0305 | . | 1 | 1 | 1 |
What I want:
UUID | Day | Vent_status |
UU0300 | 0 | 0 |
UU0300 | 1 | 1 |
UU0300 | 2 | 1 |
UU0300 | 3 | 0 |
UU0301 | 0 | 0 |
UU0301 | 1 | 0 |
UU0301 | 2 | 1 |
UU0301 | 3 | 1 |
UU0305 | 0 | . |
UU0305 | 1 | 1 |
UU0305 | 2 | 1 |
UU0305 | 3 | 1 |
I think I am having trouble with the days part. Some of the data is missing and I want to conserve the days (in this case, everyone should have day 0 - day 3 even if data is missing)
Thank you so much in advance!
data have;
input UUID $ Vent_day_0 Vent_day_1 Vent_day_2 Vent_day_3 ;
cards;
UU0300 0 1 1 0
UU0301 0 0 1 1
UU0305 . 1 1 1
;
data want;
set have;
array v(0:3) Vent_day_0--Vent_day_3;
do day=0 to 3;
Vent_status=v(day);
output;
end;
keep uuid day vent_status;
run;
How about like this:
proc transpose data=have name=day out=want(rename=col1=Vent_status);
by UUID;
var Vent_day_:;
run;
data want;
set want;
day = compress(day,,"kd");
run;
(untested)
data have;
input UUID $ Vent_day_0 Vent_day_1 Vent_day_2 Vent_day_3 ;
cards;
UU0300 0 1 1 0
UU0301 0 0 1 1
UU0305 . 1 1 1
;
data want;
set have;
array v(0:3) Vent_day_0--Vent_day_3;
do day=0 to 3;
Vent_status=v(day);
output;
end;
keep uuid day vent_status;
run;
proc transpose out=trans (rename=(COL1=Vent_status));
by uuid;
run;
data want (drop=_NAME_);
set trans;
length Day 4;
Day=put(scan(_name_,-1,'_'),3.);
;
run;
proc print;
run;
You already have three good answers, but here is a fourth. It uses the untranspose macro and would really simplify the task if you were untransposing more than one variable.
filename ut url 'http://tiny.cc/untranspose_macro';
%include ut ;
%untranspose(data=have, out=want (rename=(day_=Vent_status)),
by=UUID, prefix=Vent_, id=Day,
var=day_, missing=yes, var_first=yes)
Art
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.