topic Dates in an array in SAS Programming

Dates in an array

Don_Kros — Thu, 04 Jun 2015 00:26:53 GMT

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

Re: Dates in an array

Ron_MacroMaven — Thu, 04 Jun 2015 11:55:29 GMT

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

Re: Dates in an array

RW9 — Thu, 04 Jun 2015 12:05:21 GMT

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;

Re: Dates in an array

data_null__ — Thu, 04 Jun 2015 12:34:25 GMT

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;

Re: Dates in an array

Ksharp — Thu, 04 Jun 2015 13:47:43 GMT

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

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