can anyone suggest a solution for creating a set of binary mmmyyyy dummys using a do loop macro or array for years 2000-2016. variables should look like 1 if jan2000 0 otherwise. can i do this with multiplication if i somehow had a variable with each 1 for each year and 1 for each month then multiply year * month and if both are 1 the variable will be 1 and zero otherwise?
%let begin=2000;
%let end=2016;
%let month=month
%let year=year
options nomprint nosymbolgen;
%macro loop(start, stop);
%do i=&start %to &stop;
data yearmonthdummies; set data;
&month&year= if year in(&year) and month in (&month) then &month&year=1;
do year=year(start) to year(stop);
do month=month(start) to month(stop);
n+0;
output;
end;
end;
%end;
%mend;
%loop(&begin, &end);run;
quit;quit;end;
Here's one way based on a dates dataset.
You should probably also look at how indicator variables are calculated in SAS.
http://blogs.sas.com/content/iml/2016/02/22/create-dummy-variables-in-sas.html
*Your sample data;
data have;
do year=2000 to 2016;
do month=1 to 12;
date=mdy(month, 1, year);
format date monyy7.;
x=0;
output;
end;
end;
run;
*Transpose to a wide format;
proc transpose data=have out=wide prefix=D_;
id date;
idlabel date;
var x;
run;
*Merge in with original values;
data merged;
set have;
if _n_=1 then
set wide;
run;
*Create indicator variables;
data want2;
set merged;
array dates(*) D_:;
index=(Year - 2000)*12 + month;
dates(index)=1;
drop x _name_;
run;
Please include a small data sample, input and what you're looking for as output.
Why are month and year set to the words month/year? Are those variable names?
all i have are the years and months from 2000 to 2016
Year month
2000 1
2000 2
2000 3
…
2000 12
2001 1
2001 2
2001 3
…
2001 12
2002 1
want something that looks like this:
year month jan2000 feb2000 mar2000 apr2000 etc jan2001 feb2001 etc
2000 1 1 0 0 0 0 0
2000 2 0 1 0 0 0 0
2001 1 0 0 0 0 1 0
2001 2 0 0 0 0 0 1
If you're using years and months, the following change is necessary. You could do the logic without converting to dates, but I assumed you had date variables.
%macro yymmDummies(s, e);
%do yr = &s %to &e;
%do mo = 1 %to 12;
%let yymm = %sysfunc(mdy(&mo, 1, &yr));
Dum_%sysfunc(putn(&yymm, monname3.))&yr = (mdy(month, 1, year) = &yymm);
%end;
%end;
%mend yymmDummies;
data fakeData;
do year = 2016 to 2017;
do month = 1 to 12;
output;
end;
end;
run;
options mprint;
data withDummies;
set fakeData;
%yymmDummies(2016, 2017)
run;
Here's one way based on a dates dataset.
You should probably also look at how indicator variables are calculated in SAS.
http://blogs.sas.com/content/iml/2016/02/22/create-dummy-variables-in-sas.html
*Your sample data;
data have;
do year=2000 to 2016;
do month=1 to 12;
date=mdy(month, 1, year);
format date monyy7.;
x=0;
output;
end;
end;
run;
*Transpose to a wide format;
proc transpose data=have out=wide prefix=D_;
id date;
idlabel date;
var x;
run;
*Merge in with original values;
data merged;
set have;
if _n_=1 then
set wide;
run;
*Create indicator variables;
data want2;
set merged;
array dates(*) D_:;
index=(Year - 2000)*12 + month;
dates(index)=1;
drop x _name_;
run;
See if the following is doing what you want and then modify as necessary for your data and desired variable names.
%macro yymmDummies(s, e);
%do yr = &s %to &e;
%do mo = 1 %to 12;
%let yymm = %sysfunc(mdy(&mo, 1, &yr));
Dum_%sysfunc(putn(&yymm, monname3.))&yr = (intnx('month', date, 0, 'B') = &yymm);
%end;
%end;
%mend yymmDummies;
data fakeData;
format date date.;
do date = '01JAN2010'd to '31DEC2012'd by 20;
output;
end;
run;
options mprint;
data withDummies;
set fakeData;
%yymmDummies(2010, 2012)
run;
This is also a great and interesting solution but I would have to figure out nodupkey to get rid of the duplicates. Thanks!
@Josie1 wrote:
This is also a great and interesting solution but I would have to figure out nodupkey to get rid of the duplicates. Thanks!
You never mentioned anything about duplicates. This is why you should post sample data 🙂
not your solution, it worked great. the other solution created duplicates when i ran it,
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.