how to take missing numbers subject and visit wise

[Raj_C]

Hi SAS Team,

by taking num field as reference we need to pull the missing numbers in the output by subj and week wise.
for sub 01 and week w1 we have 1,2&5 numbers so here 3&4 are missing.
for sub 01 and week w2 we have 1,5&3 numbers so here 2&4 are missing.
for sub 02 and week w3 we have 7&5 numbers so here 1,2,3,4&6 are missing.

data Have;
input sub week$ num;
cards;
01 w1 1
01 w1 2
01 w1 5
01 w2 1
01 w2 5
01 w2 3
02 w3 7
02 w3 5
;
run;

so all these missing numbers should come in the output as shown below.

sub	week	num
1	w1	3
1	w1	4
1	w2	2
1	w2	4
2	w3	1
2	w3	2
2	w3	3
2	w3	4
2	w3	6

Regards,
Raju

[Reeza]

How do you want the max should be? Based on the maximum in the data?

[Reeza]

And are you ultimately trying to add these in to the data set or just want a subset with missing values?

[Raj_C]

if we have a subset with missing values this would be fine.

[Raj_C]

yes, the maximum value should be maximum in the data.

[novinosrin · Accepted solution]

Not the most elegant, but i think mine should work for your sample:

data Have;

input sub week $ num;

cards;

01 w1 1

01 w1 2

01 w1 5

01 w2 1

01 w2 5

01 w2 3

02 w3 7

02 w3 5

;

run;

proc sort data= have out=_have;

by sub week num;

run;

data want;

do until(last.sub);

     do _n_=1 by 1 until(last.week);

           set _have;

           by sub week;

           if _n_ ne num then do;

                do _num=_n_ to num-1;

                output;

                end;

                _n_=_num;

           end;

           else call missing(_num);

     end;

end;

keep sub week _num;

run;

[Reeza]

Not remotely as efficient as @novinosrin but easy to implement and follow:

data Have;
    input sub week $ num;
    status=1;
    cards;
01 w1 1
01 w1 2
01 w1 5
01 w2 1
01 w2 5
01 w2 3
02 w3 7
02 w3 5
;
run;

*find max num per sub/week;
proc means data=have nway noprint;
    class sub week;
    var num;
    output out=max_num max(num)=max_num;
run;

*create dataset with all values;
data want_empty;
    set max_num;
    status=0;

    do num=1 to max_num;
        output;
    end;
    drop _: max_num;
run;

proc sort data=have;
    by sub week num;
run;

*identify new obs;
data want;
    update want_empty have;
    by sub week num;
run;

[Raj_C]

Thank you @Reeza 🙂

[Raj_C]

Thank you @novinosrin 🙂

[Ksharp]

data Have;
input sub week$ num;
cards;
01 w1 1
01 w1 2
01 w1 5
01 w2 1
01 w2 5
01 w2 3
02 w3 7
02 w3 5
;
run;
proc sort data=have;
by sub week num;
run;
proc sort data=have out=temp(keep=sub week) nodupkey;
by sub week;
run;
data temp;
 set temp;
 retain num 0;
run;
data _have;
 merge have temp;
 by sub week num;
run;
data want;
 merge _have _have(firstobs=2 
 rename=(sub=_sub week=_week num=_num));

if sub=_sub and week=_week then do;
 do i=num+1 to _num-1;
  output;
 end;
end;
drop _: num;
run;