topic Adding dummy data to the dataset in SAS Programming

Adding dummy data to the dataset

sameer112217 — Mon, 14 Jan 2019 12:22:43 GMT

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

Re: Adding dummy data to the dataset

Kurt_Bremser — Mon, 14 Jan 2019 12:33:28 GMT

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;

Re: Adding dummy data to the dataset

novinosrin — Mon, 14 Jan 2019 14:28:42 GMT

@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;

Re: Adding dummy data to the dataset

sameer112217 — Tue, 15 Jan 2019 09:50:09 GMT

thanks both of you