Do Loop in SAS SQL

Question

I want to create Age variable for 2012 to 2022 by using the condition, I can do it one by one by using YEAR(calculated td-1), YEAR(calculated td), YEAR(calculated td+1),YEAR(calculated td+2) but it is lengthy process.There is year in table2 which is duplicate. Please help me to do in a single step.

Here is sample code:

proc sql;

create table Forecast1(DROP=td) as

select T1.*

,date() as td

,case 
when (YEAR(calculated td))- year(T1.Engine)<=10 then '0-10'
when (YEAR(calculated td))- year(T1.Engine)<=20 then '10-20' 
when (YEAR(calculated td))- year(T1.Engine)<=35 then '20-35' 
else '35p' end as Age_2013 format=$char5.
from gopi1.FLEET_MASTER  T1   
left join gopi1.ANNUAL_FH_AVERAGE T2
on T1.Model=T2.Model and T1.Age_bracket=T2.Age_bracket 

Answer 1

If you're in SAS then you should do this in the data step. You could do it in a view and then read that into SQL, if you prefer.

data fleet_master_vw/view=fleet_master_vw;
set fleet_master;
array ageyr age_2012 - age_2022;
do _t = 1 to dim(ageyr);
  ageyr[_t] = (your age calculation);
end;
drop _t;
run;

proc sql;
 ... your sql stuff, using fleet_master_vw as your source dataset ...

I assume fleet_master contains your ages - change to the other one if needed.