topic Re: Macro to create dummy variables in SAS Programming

Macro to create dummy variables

DangIT — Mon, 06 May 2013 16:28:03 GMT

Hi,

My dataset looks like

ID JoinDate Churn_Date Tenure

1 12Nov2011 31Dec2011 2

2 14Dec2011 30Apr2012 5

...

Churn Date is always month end, Tenure is number of months between joinDate and Churn_Date from the first day of month of JoinDate.

I'm trying to create dummy variables for reference of the MonthEnd date from JoinDate, my ideal dataset would look like:

ID JoinDate Churn_Date Tenure MonthEnd_1 MonthEnd_2 MonthEnd_

1 12Nov2011 31Dec2011 2 31Nov2011 31Dec2011

2 14Dec2011 30Apr2012 5 31Dec2011 31Jan2012 29Feb2012 ...

the X will be defined by the number of months Tenure and the function i'm using to get MonthEnd date is:

MonthEnd_1 = intnx('month',joindate,0,'e');

MonthEnd_2 = intnx('month',joindate,1,'e');

etc.

Because Tenure will be dynamic month to month, I was hoping to create a macro to automate this process of creating these dummy variables in my dataset, any help is greatly appreciated.

Thank you!

Re: Macro to create dummy variables

ballardw — Mon, 06 May 2013 16:42:17 GMT

Depending on your data ranges a macro may not be needed. Please see this thread for similar https://communities.sas.com/thread/43560

Re: Macro to create dummy variables

DangIT — Mon, 06 May 2013 16:51:12 GMT

The Tenure variable currently ranges from 1 to a max of 77 months.

Re: Macro to create dummy variables

Tom — Mon, 06 May 2013 18:59:00 GMT

data want ;

set have ;

array MonthEnd_ (77) ;

format MonthEnd_: date9. ;

do _n_=1 to tenure ;

MonthEnd_(_n_) = intnx('month',joindate,_n_-1,'e');

end;

run;

Now if you want you can make the 77 above be dynamic by finding the maximum value of TENURE from the data.

proc sql noprint ;

select max(1, max(tenure) ) into :maxtenure

from have

;

quit;

...

array MonthEnd_ (&maxtenure) ;

Re: Macro to create dummy variables

DangIT — Mon, 06 May 2013 19:25:41 GMT

Thank you very much.

Is the sql step equivalent to creating the maxtenure parameter?

%let maxtenure=77;