DATA Step, Macro, Functions and more

Dates in an array

Reply
N/A
Posts: 1

Dates in an array

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:

IDStartEnd
40000000337C004/1/20145/31/2015
40000000522C005/1/20145/31/2015
40000000665C0012/1/20145/31/2015

Desired Output:

IDMonth
40000000337C00Apr-14
40000000337C00May-14
40000000337C00Jun-14
40000000337C00Jul-14
40000000337C00Aug-14
40000000337C00Sep-14
40000000337C00Oct-14
40000000337C00Nov-14
40000000337C00Dec-14
40000000337C00Jan-15
40000000337C00Feb-15
40000000337C00Mar-15
40000000337C00Apr-15
40000000337C00May-15
40000000522C00May-14
40000000522C00Jun-14
40000000522C00Jul-14
40000000522C00Aug-14
40000000522C00Sep-14
40000000522C00Oct-14
40000000522C00Nov-14
40000000522C00Dec-14
40000000522C00Jan-15
40000000522C00Feb-15
40000000522C00Mar-15
40000000522C00Apr-15
40000000522C00May-15
40000000522C00May-14
40000000665C00Dec-14
40000000665C00Jan-15
40000000665C00Feb-15
40000000665C00Mar-15
40000000665C00Apr-15
40000000665C00May-15
40000000665C00May-14
Regular Contributor
Posts: 200

Re: Dates in an array

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);

Super User
Super User
Posts: 7,413

Re: Dates in an array

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;

Respected Advisor
Posts: 3,777

Re: Dates in an array

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;
proc print;
  
run;
data monthly;
   set test;
   do m = 0 to intck('MONTH',start,end);
      month = intnx('MONTH',start,m,'BEGIN');
     
output;
     
end;
  
format month monyy.;
  
run;
proc print;
  
run;


6-4-2015 7-32-52 AM.png
Super User
Posts: 9,687

Re: Dates in an array

Code: Program

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;

Results: Program

140000000337C00APR2014
240000000337C00MAY2014
340000000337C00JUN2014
440000000337C00JUL2014
540000000337C00AUG2014
640000000337C00SEP2014
740000000337C00OCT2014
840000000337C00NOV2014
940000000337C00DEC2014
1040000000337C00JAN2015
1140000000337C00FEB2015
1240000000337C00MAR2015
1340000000337C00APR2015
1440000000337C00MAY2015
1540000000522C00MAY2014
1640000000522C00JUN2014
1740000000522C00JUL2014
1840000000522C00AUG2014
1940000000522C00SEP2014
2040000000522C00OCT2014
2140000000522C00NOV2014
2240000000522C00DEC2014
2340000000522C00JAN2015
2440000000522C00FEB2015
2540000000522C00MAR2015
2640000000522C00APR2015
2740000000522C00MAY2015
2840000000665C00DEC2014
2940000000665C00JAN2015
3040000000665C00FEB2015
3140000000665C00MAR2015
3240000000665C00APR2015
3340000000665C00MAY2015

Xia Keshan

Ask a Question
Discussion stats
  • 4 replies
  • 228 views
  • 0 likes
  • 5 in conversation