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
Please add the expected result to your post. Some assumptions made:
- 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;
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.