Adding dummy data to the dataset

[sameer112217]

I want to add the dummy data for a task

id  city              country      month                  Amount

101  Mumbai   India         01ARR2015        100

101  Mumbai   India         01MAY2015         200

102  Delhi        India         01JUL2015         500

102  Delhi        India         01DEC2015        400

I want to add the dummy data for the following id 101 and 102 and want the below output

id  city              country      month                Amount

101  Mumbai   India         01ARR2015        100

101  Mumbai   India         01MAY2015        100

101  Mumbai   India         01JUN2015        

101  Mumbai   India         01JUL2015        

101  Mumbai   India         01AUG015        

101  Mumbai   India         01SEP2015        

101  Mumbai   India         01OCT2015        

101  Mumbai   India         01NOV2015      

101  Mumbai   India         01DEC2015        

101  Mumbai   India         01JAN2016        

101  Mumbai   India         01FEB2016        

101  Mumbai   India         01MAR016        

102  Delhi        India         01APR2015  

102  Delhi        India         01MAY2015 

102  Delhi        India         01JUN2015 

102  Delhi        India         01JUL2015     500

102  Delhi        India         01AUG2015 

102  Delhi        India         01SEP2015 

102  Delhi        India         01OCT2015 

102  Delhi        India         01NOV2015 

102  Delhi        India         01DEC2015  400

102  Delhi        India         01JAN2016 

102  Delhi        India         01FEB2016 

102  Delhi        India         01MAR2016 

In short my financial year starts from April and ends in Mar. How would I insert those dummy data for all financial year with amount zero against it. I have figured out one way is to create master table for all months and use cartesian join . Is there any other way

[Kurt_Bremser]

Create a lookup table for all months in your fiscal year, and merge that with your dataset:

%let begdate=01apr2015;
%let enddate=01mar2016;

proc sort data=have;
by id city month;
run;

data codes;
set have (keep=id city);
by id city;
if last.city;
run;

data all_months;
set codes;
month = "&begdate."d;
do until (month gt "&enddate."d);
  output;
  month = intnx('month',month,1,'b');
end;
run;

data want;
merge
  have
  all_months
;
by id city month;
run;

[novinosrin · Accepted solution]

@sameer112217  I like your cartesian idea

data have;
input id  city      $        country $     month    : date9.              Amount;
format month date9.;
cards;
101  Mumbai   India         01APR2015        100
101  Mumbai   India         01MAY2015         200
102  Delhi        India         01JUL2015         500
102  Delhi        India         01DEC2015        400
;
 
%let start='01apr2015'd;
%let end='01mar2016'd;
data fin_year;
do _n_=0 to   intck('month',&start,&end);
month=intnx('month',&start,_n_);
output;
end;
format month date9.;
run;
proc sql;
create table want as
select a.*, coalesce(b.amount,0) as amount
from (select * from (select distinct  id , city ,  country from have), fin_year) a
left join 
have b
on a.id=b.id and a.city=b.city and a.country=b.country and a.month=b.month;
quit;

[sameer112217]

thanks both of you