BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Raj_C
Obsidian | Level 7

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

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

9 REPLIES 9
Reeza
Super User

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

Reeza
Super User

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

Raj_C
Obsidian | Level 7

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

Raj_C
Obsidian | Level 7

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

novinosrin
Tourmaline | Level 20

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
Super User

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
Obsidian | Level 7

Thank you @Reeza 🙂

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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