SAS Output Data between dates

Question

I have a table of customers with effective and termination dates. Instead of having one record, I am trying to create a table that has one record per year & month for each month the member is active. Is there a simple way to create the desired table in SAS? FYI, the raw table will have a few thousand records, and will about a dozen columns, but wanted to keep the example simple for now. Also, there's no line in between the records in the desired table, just added for ease of reference. Thanks.

Raw and Desire Tables

Raw Table
Name Eff_date Term_Date
John 1/1/2020 3/31/2020
Amy 3/1/2019 3/10/2019

Desired Table
Name Eff_date Term_Date Eff_YM

John 1/1/2020 3/31/2020 202001

John 1/1/2020 3/31/2020 202002

John 1/1/2020 3/31/2020 202003

Amy 3/1/2019 3/10/2019 201903

Hi, welcome to the site. We're not a code writing service, but a code help services, so make sure your question includes a code problem and the attempt you've made to solve that problem, and specific questions about what you think is wrong or what you don't understand about the problem. See our faq and the help center for more information. Thanks, and welcome! — Joe
– Joe, Commented Nov 4, 2020 at 17:26

Reeza · Accepted Answer · 2020-11-04 18:52:12Z

1

INTCK() will find the number of months between two dates
INTNX() will increment dates by a specified amount, ie 1 month, 1 week

An explicit OUTPUT statement will write a new line for each month

 data have;
 informat name $8. date_start date_end mmddyy10.;
 format name $8. date_start date_end mmddyy10.;
 input Name date_start date_end;
 cards;
 John 1/1/2020 3/31/2020
 Amy 3/1/2019 3/10/2019
 ;;;;
run;

 data want;
 set have;
 nMonths=intck('month', date_start, date_end, 'C');

 do i=0 to nMonths;
     Effective_YM=intnx('month', date_start, i, 's');
     format effective_YM yymmn6.;
     OUTPUT;
 end;
 drop nMonths;
 run;

answered Nov 4, 2020 at 18:52

Reeza

21.4k4 gold badges26 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SAS Output Data between dates

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related