DATA Step, Macro, Functions and more

Macro to create dummy variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Macro to create dummy variables

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!


Accepted Solutions
Solution
‎05-06-2013 02:59 PM
Super User
Super User
Posts: 7,062

Re: Macro to create dummy variables

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

View solution in original post


All Replies
Super User
Posts: 11,343

Re: Macro to create dummy variables

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

Frequent Contributor
Posts: 81

Re: Macro to create dummy variables

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

Solution
‎05-06-2013 02:59 PM
Super User
Super User
Posts: 7,062

Re: Macro to create dummy variables

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

Frequent Contributor
Posts: 81

Re: Macro to create dummy variables

Thank you very much.

Is the sql step equivalent to creating the maxtenure parameter?

%let maxtenure=77;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 1260 views
  • 0 likes
  • 3 in conversation