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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.