Month-Year Loop

[Josie1]

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;

[Reeza]

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?

[Josie1]

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

[collinelliot]

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;

[Reeza · Accepted solution]

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;

[collinelliot]

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;
            
            

[Josie1]

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

[Reeza]

---

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

[Josie1]

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