SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DangIT
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

4 REPLIES 4
ballardw
Super User

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

DangIT
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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

DangIT
Fluorite | Level 6

Thank you very much.

Is the sql step equivalent to creating the maxtenure parameter?

%let maxtenure=77;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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