Hello all -
I have a question, I have a dataset that contains a unique ID, a Start Date, and an End Date. What I need to do is create another dataset with the unique member ID populated for a corresponding number of rows for the time between the start and end date. An example is below - anyone have any thoughts??
Original Data:
ID | Start | End |
40000000337C00 | 4/1/2014 | 5/31/2015 |
40000000522C00 | 5/1/2014 | 5/31/2015 |
40000000665C00 | 12/1/2014 | 5/31/2015 |
Desired Output:
ID | Month |
40000000337C00 | Apr-14 |
40000000337C00 | May-14 |
40000000337C00 | Jun-14 |
40000000337C00 | Jul-14 |
40000000337C00 | Aug-14 |
40000000337C00 | Sep-14 |
40000000337C00 | Oct-14 |
40000000337C00 | Nov-14 |
40000000337C00 | Dec-14 |
40000000337C00 | Jan-15 |
40000000337C00 | Feb-15 |
40000000337C00 | Mar-15 |
40000000337C00 | Apr-15 |
40000000337C00 | May-15 |
40000000522C00 | May-14 |
40000000522C00 | Jun-14 |
40000000522C00 | Jul-14 |
40000000522C00 | Aug-14 |
40000000522C00 | Sep-14 |
40000000522C00 | Oct-14 |
40000000522C00 | Nov-14 |
40000000522C00 | Dec-14 |
40000000522C00 | Jan-15 |
40000000522C00 | Feb-15 |
40000000522C00 | Mar-15 |
40000000522C00 | Apr-15 |
40000000522C00 | May-15 |
40000000522C00 | May-14 |
40000000665C00 | Dec-14 |
40000000665C00 | Jan-15 |
40000000665C00 | Feb-15 |
40000000665C00 | Mar-15 |
40000000665C00 | Apr-15 |
40000000665C00 | May-15 |
40000000665C00 | May-14 |
assuming your dates are SAS dates and therefore integers
mid-way-date = ((end - start)/2) + start;
figure out whether you want to floor or ceiling that calculation to deal with the fraction 1/2
mid-way-date = floor(((end - start)/2) + start);
Then you want a simple do loop and output step:
data have;
id="40000000337C00"; start="01APR2014"d; end="31MAY2015"d; output;
run;
data want;
set have;
length result $20;
do until (month(start)=month(end) and year(start)=year(end));
result=put(month(start),2.)||"-"||put(year(start),4.);
output;
start=intnx('month',start,1,'same');
end;
run;
Date interval functions.
data test;
infile cards expandtabs;
input id:$15. (Start End)(:mmddyy.);
format start end mmddyy.;
cards;
40000000337C00 4/1/2014 5/31/2015
40000000522C00 5/1/2014 5/31/2015
40000000665C00 12/1/2014 5/31/2015
;;;;
run;
data want;
set test;
do i=start to end;
month=month(i);
if month ne lag(month) or i=start then output;
end;
format i monyy7.;
drop month start end;
run;
proc print;run;
1 | 40000000337C00 | APR2014 |
---|---|---|
2 | 40000000337C00 | MAY2014 |
3 | 40000000337C00 | JUN2014 |
4 | 40000000337C00 | JUL2014 |
5 | 40000000337C00 | AUG2014 |
6 | 40000000337C00 | SEP2014 |
7 | 40000000337C00 | OCT2014 |
8 | 40000000337C00 | NOV2014 |
9 | 40000000337C00 | DEC2014 |
10 | 40000000337C00 | JAN2015 |
11 | 40000000337C00 | FEB2015 |
12 | 40000000337C00 | MAR2015 |
13 | 40000000337C00 | APR2015 |
14 | 40000000337C00 | MAY2015 |
15 | 40000000522C00 | MAY2014 |
16 | 40000000522C00 | JUN2014 |
17 | 40000000522C00 | JUL2014 |
18 | 40000000522C00 | AUG2014 |
19 | 40000000522C00 | SEP2014 |
20 | 40000000522C00 | OCT2014 |
21 | 40000000522C00 | NOV2014 |
22 | 40000000522C00 | DEC2014 |
23 | 40000000522C00 | JAN2015 |
24 | 40000000522C00 | FEB2015 |
25 | 40000000522C00 | MAR2015 |
26 | 40000000522C00 | APR2015 |
27 | 40000000522C00 | MAY2015 |
28 | 40000000665C00 | DEC2014 |
29 | 40000000665C00 | JAN2015 |
30 | 40000000665C00 | FEB2015 |
31 | 40000000665C00 | MAR2015 |
32 | 40000000665C00 | APR2015 |
33 | 40000000665C00 | MAY2015 |
Xia Keshan
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.