I have the following table. What I would like to do is that if there is missing value between min and max day per subject, I would like to insert 0
subject | value | day |
1 | 5 | 1 |
1 | 5 | 2 |
1 | 1 | 3 |
1 | 2 | 5 |
2 | 5 | 1 |
2 | 5 | 2 |
2 | 4 | 4 |
2 | 3 | 5 |
subject | value | day |
1 | 5 | 1 |
1 | 5 | 2 |
1 | 1 | 3 |
1 | 0 | 4 |
1 | 2 | 5 |
2 | 5 | 1 |
2 | 5 | 2 |
2 | 0 | 3 |
2 | 4 | 4 |
2 | 3 | 5 |
To be honest, i have no idea how to start. I was thinking to transpose and then assign it of 0 but it is too tedious
Create all possible combinations of subject and day. Merge this with the original data set. If value is missing, set value to be zero.
data have;
input subject value day;
cards;
1 5 1
1 5 2
1 1 3
1 2 5
2 5 1
2 5 2
2 4 4
2 3 5
;
proc sql;
create table distinct_day as select distinct day from have;
create table distinct_subj as select distinct subject from have;
create table subj_day as select * from distinct_subj,distinct_day;
run;
data want;
merge have subj_day;
by subject day;
if missing(value) then value=0;
run;
This assumes original data is sorted properly by subject and day.
You can use MERGE with a FIRSTOBS option to let you look ahead to find the DAY for the next record:
data have;
input subject value day;
cards;
1 5 1
1 5 2
1 1 3
1 2 5
2 5 1
2 5 2
2 4 4
2 3 5
run;
data want (drop=nxt_:);
merge have have (firstobs=2 keep=day rename=(day=nxt_day));
output;
if nxt_day>day+1 then do day=day+1 to nxt_day-1;
value=0;
output;
end;
run;
This program assumes the data are sorted by subject/day. And that every subject starts with a DAY value that is not greater than the DAY value ending the prior subject.
data have;
input subject value day;
cards;
1 5 1
1 5 2
1 1 3
1 2 5
2 5 1
2 5 2
2 4 4
2 3 5
run;
data want(drop=_:);
merge have
have(firstobs=2 keep=subject day rename=(subject=_subject day=_day));
output;
if subject=_subject then do;
do day=day+1 to _day-1;
value=0;
output;
end;
end;
run;
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 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.