Solved
Contributor
Posts: 36

# how to take missing numbers subject and visit wise

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

Accepted Solutions
Highlighted
Solution
‎12-07-2017 08:02 PM
PROC Star
Posts: 1,100

## Re: how to take missing numbers subject and visit wise

[ Edited ]

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;

All Replies
Super User
Posts: 22,582

## Re: how to take missing numbers subject and visit wise

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

Super User
Posts: 22,582

## Re: how to take missing numbers subject and visit wise

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

Contributor
Posts: 36

## Re: how to take missing numbers subject and visit wise

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

Contributor
Posts: 36

## Re: how to take missing numbers subject and visit wise

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

Highlighted
Solution
‎12-07-2017 08:02 PM
PROC Star
Posts: 1,100

## Re: how to take missing numbers subject and visit wise

[ Edited ]

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;

Super User
Posts: 22,582

## Re: how to take missing numbers subject and visit wise

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;``````
Contributor
Posts: 36

## Re: how to take missing numbers subject and visit wise

Thank you @Reeza

Contributor
Posts: 36

## Re: how to take missing numbers subject and visit wise

Thank you @novinosrin

Super User
Posts: 10,526

## Re: how to take missing numbers subject and visit wise

[ Edited ]
``````
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;``````
☑ This topic is solved.