sas dates

[sathya66]

Hi ,

our financial year is between 1st July to 30th jun. I have one  column  name date . I need to create the year column based the dates.

see example:

from 1st July 2018 to 30th Jun 2019 it is 2019

from 1st July 2019 to 30th Jun 2020 it is 2020

if we go to next year it should be 2021

date	year
25/06/2019	2019
26/06/2019	2019
27/06/2019	2019
28/06/2019	2019
29/06/2019	2019
30/06/2019	2019
01/07/2019	2020
02/07/2019	2020
03/07/2019	2020
04/07/2019	2020
05/07/2019	2020
06/07/2019	2020
07/07/2019	2020
08/07/2019	2020
09/07/2019	2020
10/07/2019	2020
11/07/2019	2020
12/07/2019	2020
13/07/2019	2020
14/07/2019	2020
15/07/2019	2020
16/07/2019	2020
17/07/2019	2020
18/07/2019	2020
19/07/2019	2020
20/07/2019	2020

looking for code in proc sql.

thanks,

SS

[novinosrin]

data have;
input date	:ddmmyy10.;
format date ddmmyy10.;
cards;
25/06/2019	2019
26/06/2019	2019
27/06/2019	2019
28/06/2019	2019
29/06/2019	2019
30/06/2019	2019
01/07/2019	2020
02/07/2019	2020
03/07/2019	2020
04/07/2019	2020
05/07/2019	2020
06/07/2019	2020
07/07/2019	2020
08/07/2019	2020
09/07/2019	2020
10/07/2019	2020
11/07/2019	2020
12/07/2019	2020
13/07/2019	2020
14/07/2019	2020
15/07/2019	2020
16/07/2019	2020
17/07/2019	2020
18/07/2019	2020
19/07/2019	2020
20/07/2019	2020
;

data want;
set have;
year=year(date)+(month(date)>6);
run;

[novinosrin · Accepted solution]

Oops SQL

proc sql;
create table want as
select *,year(date)+(month(date)>6) as year
from have
order by date;
quit;

You need an order by in SQL though as the order is never guaranteed in SQL, so an extra overhead as opposed to datastep

[ballardw]

In SQL this should work if your date values are SAS dates.

(year(date) +(month(date) ge 7)) as FY

This works because the comparison will return a one when true when testing the month of the year for the date

[Ksharp]

data have;
input date	:ddmmyy10. year;
format date ddmmyy10.;
cards;
25/06/2019	2019
26/06/2019	2019
27/06/2019	2019
28/06/2019	2019
29/06/2019	2019
30/06/2019	2019
01/07/2019	2020
02/07/2019	2020
03/07/2019	2020
04/07/2019	2020
05/07/2019	2020
06/07/2019	2020
07/07/2019	2020
08/07/2019	2020
09/07/2019	2020
10/07/2019	2020
11/07/2019	2020
12/07/2019	2020
13/07/2019	2020
14/07/2019	2020
15/07/2019	2020
16/07/2019	2020
17/07/2019	2020
18/07/2019	2020
19/07/2019	2020
20/07/2019	2020
;

data want;
set have;
want_year=year(intnx('year.7',date,1));
run;