Help using Base SAS procedures

Parsing out date9. fields for table join

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Parsing out date9. fields for table join

I'm needing to use a termination date in date9. format to join to another table that has monthly records with a time_id also in date9. format.  The problem is that the time_id always references the last day of the month (31JAN2015, 28FEB2015, etc.) and my termination date can be any day of the month.  I just need to use a member_id in combination with the termination month/year to join to the other table with member_id and month/year, disregarding day of the month.  SUBSTR requires characters and the dates are numeric.  I tried using MONTH(TERM_DT) but that gives the number of the month and I need the 3 character format for month.  Any other ideas?


Accepted Solutions
Solution
‎12-22-2015 08:26 AM
Super User
Posts: 17,819

Re: Parsing out date9. fields for table join

Use the INTNX function to move your date to the last of the month.

intnx('month', date, 0, 'e')

View solution in original post


All Replies
Solution
‎12-22-2015 08:26 AM
Super User
Posts: 17,819

Re: Parsing out date9. fields for table join

Use the INTNX function to move your date to the last of the month.

intnx('month', date, 0, 'e')
Occasional Contributor
Posts: 6

Re: Parsing out date9. fields for table join

Thanks - this worked perfectly!  I didn't know just how versatile intnx was...

Super User
Posts: 17,819

Re: Parsing out date9. fields for table join

Or the PUT function and a year month format.

put(termination_date, yymon7.)=put(time_id, yymon7.);
Super User
Posts: 9,676

Re: Parsing out date9. fields for table join

It would be better to post some data to explain your questions.  GROUPFORMAT + FORMAT is for such scenario .

 

 

data want;
 merge a b;
 by date groupformat ;
 format date monyy.;
run;

 

 

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 308 views
  • 1 like
  • 3 in conversation