DATA Step, Macro, Functions and more

how to take missing numbers subject and visit wise

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

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.

subweeknum
1w13
1w14
1w22
1w24
2w31
2w32
2w33
2w34
2w36

 

Regards,
Raju


Accepted Solutions
Solution
a week ago
PROC Star
Posts: 359

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;

View solution in original post


All Replies
Super User
Posts: 20,236

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: 20,236

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.

Solution
a week ago
PROC Star
Posts: 359

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;

Highlighted
Super User
Posts: 20,236

Re: how to take missing numbers subject and visit wise

Posted in reply to novinosrin

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 Smiley Happy

Contributor
Posts: 36

Re: how to take missing numbers subject and visit wise

Posted in reply to novinosrin

Thank you @novinosrin Smiley Happy

Super User
Posts: 10,125

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 132 views
  • 2 likes
  • 4 in conversation