I want each study included in my dataset for each phase and each subject whether they have any values that day or not. I was thinking of just merging my HAVE dataset with one that contains all the IDs, phases, and days. However, I would rather not manually input every study day with each ID and phase. Is there a quick way to do this?
data have;
input id $ phase day val @@;
cards;
A 1 6 12.1
A 1 8 15.3
A 1 10 12.0
A 1 12 13.9
A 1 14 17.1
A 2 6 21.4
A 2 8 34.9
A 2 10 18.6
A 2 16 12.4
B 1 6 14.0
B 1 8 16.1
B 1 10 15.4
B 1 14 18.3
B 2 6 21.9
B 2 8 23.9
B 2 10 17.3
B 2 12 14.6
;
run;
data want;
input id $ phase day val @@;
cards;
A 1 6 12.1
A 1 8 15.3
A 1 10 12.0
A 1 12 13.9
A 1 14 17.1
A 1 16 .
A 2 6 21.4
A 2 8 34.9
A 2 10 18.6
A 2 12 .
A 2 14 .
A 2 16 12.4
B 1 6 14.0
B 1 8 16.1
B 1 10 15.4
B 1 12 .
B 1 14 18.3
B 1 16 .
B 2 6 21.9
B 2 8 23.9
B 2 10 17.3
B 2 12 14.6
B 2 14 .
B 2 16 .
;
run;
How about
data have;
input id $ phase day val @@;
cards;
A 1 6 12.1
A 1 8 15.3
A 1 10 12.0
A 1 12 13.9
A 1 14 17.1
A 2 6 21.4
A 2 8 34.9
A 2 10 18.6
A 2 16 12.4
B 1 6 14.0
B 1 8 16.1
B 1 10 15.4
B 1 14 18.3
B 2 6 21.9
B 2 8 23.9
B 2 10 17.3
B 2 12 14.6
;
run;
data want(drop = rc);
if _N_ = 1 then do;
dcl hash h(dataset : 'have');
h.definekey('id', 'phase', 'day');
h.definedata('val');
h.definedone();
end;
set have;
by id phase;
if first.phase then do day = 6 to 16 by 2;
val = .;
rc = h.find();
output;
end;
run;
Result:
id phase day val A 1 6 12.1 A 1 8 15.3 A 1 10 12.0 A 1 12 13.9 A 1 14 17.1 A 1 16 . A 2 6 21.4 A 2 8 34.9 A 2 10 18.6 A 2 12 . A 2 14 . A 2 16 12.4 B 1 6 14.0 B 1 8 16.1 B 1 10 15.4 B 1 12 . B 1 14 18.3 B 1 16 . B 2 6 21.9 B 2 8 23.9 B 2 10 17.3 B 2 12 14.6 B 2 14 . B 2 16 .
Or shorter :
proc summary data = have completetypes nway;
class id phase day;
var val;
output out = want(drop = _:) sum =;
run;
I assume that the range of day is all from 6 to 16. You can try this:
data have1;
set have;
by id phase;
output;
if first.phase and day > 6 then do;
day = 6;
val = .;
output;
end;
if last.phase and day < 16 then do;
day = 16;
val = .;
output;
end;
run;
proc sort data=have1;
by id phase day;
run;
data want;
set have1;
by id phase;
nextrec = _n_+1;
output;
if last.phase = 0 then do;
set have1(rename=(day=nextday)) point=nextrec;
do day = day + 2 to nextday - 2 by 2;
val = .;
output;
end;
end;
drop nextday;
run;
data have;
input id $ phase day val @@;
cards;
A 1 6 12.1
A 1 8 15.3
A 1 10 12.0
A 1 12 13.9
A 1 14 17.1
A 2 6 21.4
A 2 8 34.9
A 2 10 18.6
A 2 16 12.4
B 1 6 14.0
B 1 8 16.1
B 1 10 15.4
B 1 14 18.3
B 2 6 21.9
B 2 8 23.9
B 2 10 17.3
B 2 12 14.6
;
run;
proc freq data=have noprint;
table id*phase*day/out=all_level(drop=count percent) sparse list;
run;
data want;
merge all_level have;
by id phase day;
run;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.