Proc sql;
create table Autoreg_L6M_&t1 as
select distinct objectregnbr
,case when input(put(period,yymmn6.),best32.) GE &t6 then mean(depreciation) end as Mean_depreciation_L6m
from pricedata31
where input(put(period,yymmn6.),best32.) GE &t6
and input(put(period,yymmn6.),best32.) Le &t1
group by objectregnbr
;
Quit;
Proc sql;
create table Autoreg_L6M_&t2 as
select distinct objectregnbr
,case when input(put(period,yymmn6.),best32.) GE &t7 then mean(depreciation) end as Mean_depreciation_L6m
from pricedata31
where input(put(period,yymmn6.),best32.) GE &t7
and input(put(period,yymmn6.),best32.) LE &t2
group by objectregnbr
;
Quit;
As you see this can get super repetitive with 36 months to go.
Are there any way to make this loop over and increase to the next macro variable?
The simple answer is don't model your data that way. You are trying to use SAS like Excel with a transposed thinking. Work with long (normalised data) and then transpose up only when needed, it will make your code faster and simpler. If you produce some test data and show what you want out, I can show you. The alternative is you either use array processing in a datastep (second on the list of recommendations), or thirdly write some macro loops - bad idea, dodgy coding, slow etc. not recommended.
The simple answer is don't model your data that way. You are trying to use SAS like Excel with a transposed thinking. Work with long (normalised data) and then transpose up only when needed, it will make your code faster and simpler. If you produce some test data and show what you want out, I can show you. The alternative is you either use array processing in a datastep (second on the list of recommendations), or thirdly write some macro loops - bad idea, dodgy coding, slow etc. not recommended.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.