topic Re: Month-Year Loop in SAS Programming

Month-Year Loop

Josie1 — Fri, 14 Apr 2017 17:35:10 GMT

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;

%mend;

%loop(&begin, &end);run;

quit;quit;end;

Re: Month-Year Loop

Reeza — Fri, 14 Apr 2017 18:01:50 GMT

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?

Re: Month-Year Loop

collinelliot — Fri, 14 Apr 2017 18:11:30 GMT

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;

Re: Month-Year Loop

Josie1 — Fri, 14 Apr 2017 18:14:49 GMT

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

Re: Month-Year Loop

collinelliot — Fri, 14 Apr 2017 18:20:07 GMT

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;

Re: Month-Year Loop

Reeza — Fri, 14 Apr 2017 18:36:34 GMT

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;

Re: Month-Year Loop

Josie1 — Fri, 14 Apr 2017 18:47:41 GMT

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

Re: Month-Year Loop

Reeza — Fri, 14 Apr 2017 18:48:55 GMT

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

Re: Month-Year Loop

Josie1 — Fri, 14 Apr 2017 18:51:13 GMT

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