topic Re: Proc Transpose and add days in SAS Procedures

Proc Transpose and add days

marinalev — Sat, 12 Sep 2020 02:21:23 GMT

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!

Re: Proc Transpose and add days

PGStats — Sat, 12 Sep 2020 02:52:02 GMT

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)

Re: Proc Transpose and add days

novinosrin — Sat, 12 Sep 2020 02:52:59 GMT


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;

Re: Proc Transpose and add days

ghosh — Sun, 13 Sep 2020 16:50:38 GMT

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;

Re: Proc Transpose and add days

art297 — Sun, 13 Sep 2020 19:50:55 GMT

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