topic Re: missing value between missing days in SAS Programming

missing value between missing days

radhikaa4 — Mon, 16 Dec 2019 20:02:09 GMT

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

Re: missing value between missing days

PaigeMiller — Mon, 16 Dec 2019 20:59:22 GMT

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.

Re: missing value between missing days

radhikaa4 — Tue, 17 Dec 2019 01:26:49 GMT

This is working, but what if the days are different for each subject? For example, what if first subject had d1, d2, d4 - I would like to input d3 as 0? Not necessarily all day 5 are last

Re: missing value between missing days

mkeintz — Tue, 17 Dec 2019 08:29:27 GMT

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.

Re: missing value between missing days

Ksharp — Tue, 17 Dec 2019 12:09:57 GMT

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;