DATA Step, Macro, Functions and more

how to find missing months up to four months

Reply
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

Posted in reply to thanikondharish

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;

 

 

Super User
Posts: 6,899

Re: how to find missing months up to four months

[ Edited ]
Posted in reply to thanikondharish

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 ]
Posted in reply to thanikondharish
  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
Ask a Question
Discussion stats
  • 3 replies
  • 55 views
  • 1 like
  • 4 in conversation