Iterate (loop) through multiple dates

[ankitasharma]

I want to create a new variable "newdate" in my dataset using variable "datex". 

if datex is between 01-04-2011 to 31-03-2012 then newdate= 2011-12 

loop till 2050.

is there a way in sas I could do it?

Really appreciate your help. 

[Kurt_Bremser]

Dates are days. Do you want to have all days from 2011-12-01 to 2050-12-31?

[Onizuka]

I think he wants all dates between this period per yer :

01/04/2011 --> 31/03/2012 = 2011/12

01/04/2012 --> 31/03/2013 = 2012/12

01/04/2013 --> 31/03/2014 = 2013/12

-----

01/04/2050 --> 31/03/2051 = 2050/12

or maybe he wants :

01/04/2011 --> 31/03/2012 = 2011/12

01/04/2012 --> 31/03/2013 = 2012/13

01/04/2013 --> 31/03/2014 = 2013/14

-----

01/04/2050 --> 31/03/2051 = 2050/51

[ankitasharma]

hey,

yes, sorry!! thats what i want in output.  

[Kurt_Bremser · Accepted solution]

So you want to create a "financial year" from the date. Try this:

%let startfy=4;

data have;
format datex yymmddd10.;
input datex yymmdd10.;
datalines;
2011-04-01
2011-04-02
2012-03-31
2012-04-01
;

data want;
set have;
length newdate $7;
if month(datex) >= &startfy.
then newdate = catx('/',put(year(datex),z4.),substr(put(year(datex)+1,z4.),3,2));
else newdate = catx('/',put(year(datex)-1,z4.),substr(put(year(datex),z4.),3,2));
run;

proc print data=want noobs;
run;

Result:

     datex    newdate

2011-04-01    2011/12
2011-04-02    2011/12
2012-03-31    2011/12
2012-04-01    2012/13

[ankitasharma]

thank you so much for this 🙂

[Patrick]

@ankitasharma 

The intnx() function allows you to shift SAS date, datetime and time values. Formats allow you to print such values in the form you like.

data have;
  format mydate date9.;
  do mydate='31Mar2011'd, '01Apr2011'd, '31Mar2012'd, '01Apr2012'd ;
    output;
  end;
  stop;
run;

data want;
  set have;
  format newdate yymmd7.;
  newdate=intnx('year',intnx('month',mydate,-3,'b'),0,'e');
run;

proc print data=want; 
run;