Dataset only has calendar year in numeric format. The following sql statement calculates the fiscal start year and fiscal end year based on the calendar month and year.Case statement requires the results of the ‘when’ clause is the same as the ‘then’ clause, therefor the put function can’t be used to convert bud_year to a character value.
proc sql;
create table step1 as
select A.*, B.proposed_amount, B.awarded_amount, B.period, B.start_dt as bud_start_dt, B.end_dt as bud_end_dt, B.bud_month, B.bud_year,
case
when B.bud_month>=5 then B.bud_year
when B.bud_month<5 then B.bud_year-1
else .
end as bud_fiscalyr_start,
case
when B.bud_month>=5 then B.bud_year+1
when B.bud_month<5 then B.bud_year
else .
end as bud_fiscalyr_end
from projectlist as A
left join project_budget as B
on B.project_id = A.project_id
;
quit;
step1
bud_month bud_year bud_fiscalyr_start bud_fiscalyr_end
4 2014 2013 2014
5 2014 2014 2015
proc sql;
create table step2 (drop=bud_fiscalyr_start bud_fiscalyr_end) as
select *,
case
when put(bud_fiscalyr_start,4.)=’ .’ then ”
when put(bud_fiscalyr_start,4.) ne ‘ .’ then put(bud_fiscalyr_start,4.)||”-“||put(bud_fiscalyr_end,4.)
end as bud_fiscal_year
from step1
;
quit;
step2
bud_month bud_year bud_fiscal_year
4 2014 2013-2014
5 2014 2014-2015
. .