CODEWITHSUNDEEP
datesas
NickV
NickV

Reputation: 53

How to I create a variable for between dates

I am struggling to create a financial year variable of interval dates. I want variables such as 1819 (for FY 2018/19), etc.

data mydata;
    set mydata;
    FinYear = 9999.;
    if '01JUL2018'd <= ConductedDate <= '30JUN2019'd then FinYear=1819;
run;

Is there something I am doing wrong? It is not working.

Upvotes: 0

Views: 2640

Answers (2)

TGG
TGG

Reputation: 19

you can try to modify row 4 code as:

data mydata;
set mydata;
FinYear = 9999.;
if '01JUL2018'd <= ConductedDate and ConductedDate <= '30JUN2019'd then FinYear=1819;
run;

Thanks

Upvotes: 1

Joe
Joe

Reputation: 63424

Not sure what you're doing wrong, but, this is a better way to do it regardless. Note this assumes your input variable is a numeric date variable, not a character variable - check that! If it is a character variable, you will need to INPUT it to a numeric date variable.

First we make some fake data - you should not need this step:

data have;
  format datevar date9.;
  do datevar = '02JAN2017'd to '01FEB2021'd by 20;
    output;
  end;
run;

Now we use INTNX (time interval increment function) with the YEAR.7 interval (YEAR, but shift over by 7 - in this case, 7 months - so it is every July 1) and extract the YEAR() from it. Finally we do some math to get your exact value (from the year component of that date) using the modulo (remainder) function, grabbing those last 2 digits and adding one to them.

data want;
  set have;
  fy_start = year(intnx('year.7',datevar,0));
  finyear = cats(mod(fy_start,100),mod(fy_start+1,100));
run;

I would note that I don't particularly like doing this - there are better ways, particularly using formats, to show what financial year a date value is in actually making a raw data value.

proc format;
  value finyr
  '01JUL2016'd - '30JUN2017'd = '1617'
  '01JUL2017'd - '30JUN2018'd = '1718'
  '01JUL2018'd - '30JUN2019'd = '1819'
  '01JUL2019'd - '30JUN2020'd = '1920'
  '01JUL2020'd - '30JUN2021'd = '2021'
  '01JUL2021'd - '30JUN2022'd = '2122'
  ;
quit;

data want;
  set have;
  format datevar finyr.;
run;

If you really DO need it in a value, you can always use PUT of course (newval = put(datevar finyr.);)

The format could be made programmatically, rather than by hand as above, using a CNTLIN dataset, to avoid having to update it every year.

Upvotes: 1

Related Questions