CODEWITHSUNDEEP
datedatetimesasdate-rangedays
heroz
heroz

Reputation: 13

SAS Given a start & end date I need to know the dates of each 30 day period AFTER the first 35 days

I am am given 2 dates, a start date and an end date.

I would like to know the date of the first 35 day period, then each subsequent 30 day period.

I have;

start       end
22-Jun-15   22-Oct-15
9-Jan-15    15-May-15

I want;

start       end         tik1        tik2        tik3        tik4
22-Jun-15   22-Oct-15   27-Jul-15   26-Aug-15   25-Sep-15   
9-Jan-15    15-May-15   13-Feb-15   15-Mar-15   14-Apr-15   14-May-15

I am fine with the dates calculations but my real issue is creating a variable and incrementing its name. I decided to include my whole problem because I thought it might be easier to explain in its context.

Upvotes: 1

Views: 1101

Answers (2)

G.Arima
G.Arima

Reputation: 1171

You can solve the problem via following logic:

1) Determining number of columns to be added.

2) Calculating the values for the columns basis the requirement

data test;
input start end;
informat start date9. end date9.;
format start date9. end date9.;
datalines;
22-Jun-15 22-Oct-15
09-Jan-15 15-May-15
;
run;

/*******Determining number of columns*******/
data noc_cal;
set test;
no_of_col = floor((end-start)/30);
run;
proc sql;
select max(no_of_col) into: number_of_columns from noc_cal;
run;

/*******Making an array where 1st iteration(tik1) is increased by 35days whereas others are incremented by 30days*******/
data test1;
set test;
array tik tik1-tik%sysfunc(COMPRESS(&number_of_columns.));
format tik: date9.;
tik1 = intnx('DAYS',START,35);
do i= 2 to %sysfunc(COMPRESS(&number_of_columns.));
tik[i]= intnx('DAYS',tik[i-1],30);
if tik[i] > end then tik[i]=.;
end; 
drop i;
run;

Alternate Way (incase you dont want to use proc sql)

data test;
input start end;
informat start date9. end date9.;
format start date9. end date9.;
datalines;
22-Jun-15 22-Oct-15
09-Jan-15 15-May-15
;
run;

/*******Determining number of columns*******/
data noc_cal;
set test;
no_of_col = floor((end-start)/30);
run;

proc sort data=noc_cal;
by no_of_col;
run;

data _null_;
set noc_cal;
by no_of_col;
if last.no_of_col;
call symputx('number_of_columns',no_of_col);
run;

/*******Making an array where 1st iteration(tik1) is increased by 35days whereas others are incremented by 30days*******/
data test1;
set test;
array tik tik1-tik%sysfunc(COMPRESS(&number_of_columns.));
format tik: date9.;
tik1 = intnx('DAYS',START,35);
do i= 2 to %sysfunc(COMPRESS(&number_of_columns.));
tik[i]= intnx('DAYS',tik[i-1],30);
if tik[i] > end then tik[i]=.;
end; 
drop i;
run;

My output:

> **start   |end        |tik1     | tik2     |tik3     |tik4**
> 22Jun2015 |22Oct2015  |27Jul2015| 26Aug2015|25Sep2015|    
> 09Jan2015 |15May2015  |13Feb2015| 15Mar2015|14Apr2015|14May2015

Upvotes: 2

Quentin
Quentin

Reputation: 6378

I tend to prefer long vertical structures. I would approach it like:

data want;
  set have;
  tik=start+35;
  do while(tik<=end);
    output;
    tik=tik+30;
  end;
  format tik mmddyy10.;
run;

If you really need it wide, you could transpose that dataset in a second step.

Upvotes: 0

Related Questions