topic Re: how to find missing months up to four months in SAS Programming

how to find missing months up to four months

thanikondharish — Thu, 26 Jul 2018 10:05:24 GMT

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

Re: how to find missing months up to four months

andreas_lds — Thu, 26 Jul 2018 10:20:58 GMT

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;

Re: how to find missing months up to four months

Astounding — Thu, 26 Jul 2018 10:22:48 GMT

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.

Re: how to find missing months up to four months

Kurt_Bremser — Thu, 26 Jul 2018 10:32:08 GMT

set up a reference table for months
with a cartesian join, build all possible combinations of id/month
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;