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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.