## Month-Year Loop

Solved
Occasional Contributor
Posts: 13

# Month-Year Loop

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;

Accepted Solutions
Solution
‎04-14-2017 02:46 PM
Super User
Posts: 23,778

## Re: Month-Year Loop

Posted in reply to collinelliot

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

All Replies
Super User
Posts: 23,778

## Re: Month-Year Loop

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?

Occasional Contributor
Posts: 13

## Re: Month-Year Loop

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

PROC Star
Posts: 311

## Re: Month-Year Loop

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;``````
Solution
‎04-14-2017 02:46 PM
Super User
Posts: 23,778

## Re: Month-Year Loop

Posted in reply to collinelliot

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;``````
PROC Star
Posts: 311

## Re: Month-Year Loop

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;

``````
Occasional Contributor
Posts: 13

## Re: Month-Year Loop

Posted in reply to collinelliot

This is also a great and interesting solution but I would have to figure out nodupkey to get rid of the duplicates. Thanks!

Super User
Posts: 23,778

## Re: Month-Year Loop

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

Occasional Contributor
Posts: 13

## Re: Month-Year Loop

not your solution, it worked great. the other solution created duplicates when i ran it,

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
• 8 replies
• 403 views
• 1 like
• 3 in conversation