## how to find missing months up to four months

Frequent Contributor
Posts: 77

# how to find missing months up to four months

[ Edited ]

data s ;
input id \$ month sale ;
cards ;
111 1 486
111 2 486
111 3 486
112 1 487
112 2 489
112 3 789
112 4 963
113 1 789
113 2 879
114 1 589
;

actually 111 id hasn't 4th month like that how to find missing months and assign zero value to sale variable

Valued Guide
Posts: 624

## Re: how to find missing months up to four months

- only months at the end are missing

- not only the fourth obs is added, but up to 3, so that there are four obs per id

- the source-data is sorted by id and month

``````data want;
set have;
by id;

output;

if last.id and month < 4 then do;
do month = month + 1 to 4;
sale = 0;
output;
end;
end;
run;``````

Super User
Posts: 6,899

## Re: how to find missing months up to four months

[ Edited ]

Assuming your data is in order as you have indicated:

data want;

set have;

by id;

output;

if last.id;

sale = 0;

if month < 4 then do month = month + 1 to 4;

output;

end;

run;

A few seconds apart ... great minds think alike.

Super User
Posts: 10,530

## Re: how to find missing months up to four months

[ Edited ]
1. set up a reference table for months
2. with a cartesian join, build all possible combinations of id/month
3. use that as base for a join with your original data, and set sale according to your rule
``````data months;
input month;
cards;
1
2
3
4
;
run;

proc sql;
create table lookup as
select distinct s.id, b.month
from
s, months b
;
create table want as
select
a.id,
a.month,
case
when s.sale is not missing then s.sale
else 0
end as sale
from
lookup a
left join s
on a.id=s.id and a.month=s.month
;
quit;``````

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Discussion stats
• 3 replies
• 55 views
• 1 like
• 4 in conversation