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-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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