topic Re: how to take missing numbers subject and visit wise in SAS Programming

how to take missing numbers subject and visit wise

Raj_C — Thu, 07 Dec 2017 23:32:55 GMT

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

Re: how to take missing numbers subject and visit wise

Reeza — Thu, 07 Dec 2017 23:40:38 GMT

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

Re: how to take missing numbers subject and visit wise

Reeza — Thu, 07 Dec 2017 23:41:16 GMT

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

Re: how to take missing numbers subject and visit wise

Raj_C — Thu, 07 Dec 2017 23:43:35 GMT

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

Re: how to take missing numbers subject and visit wise

Raj_C — Thu, 07 Dec 2017 23:44:38 GMT

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

Re: how to take missing numbers subject and visit wise

novinosrin — Fri, 08 Dec 2017 00:08:30 GMT

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;

keep sub week _num;

run;

Re: how to take missing numbers subject and visit wise

Reeza — Fri, 08 Dec 2017 00:53:30 GMT

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;

Re: how to take missing numbers subject and visit wise

Raj_C — Fri, 08 Dec 2017 01:09:48 GMT

Thank you @novinosrin 🙂

Re: how to take missing numbers subject and visit wise

Raj_C — Fri, 08 Dec 2017 01:11:19 GMT

Thank you @Reeza 🙂

Re: how to take missing numbers subject and visit wise

Ksharp — Sat, 09 Dec 2017 09:39:14 GMT


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;