how to find missing months up to four months

[thanikondharish]

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

[andreas_lds]

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;

[Astounding]

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.

[Kurt_Bremser]

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;