How to loop a calculation for multiple months using proc sql

[Nqobile_S14]

Hello, I am trying to loop a proc sql calculation for multiple months.

I want the loop to calculate from 2021 Dec to 2022 May.

The months are in one table but separate columns

e.g final_collection_202112, final_collections_202205

The calculation adds the collections for the individual months, calculates VAT and interest and then produces a final payment which is a sum of collections, VAT and interest.

Please see script below.

data _NULL_;

PreviousMonths = substr(left(input(put(intnx('MONTH',today(),-6,'END'),yymmddn8.),$8.)),1,6);
call symputx('PreviousMonths',PreviousMonths);

LastMonth = substr(left(input(put(intnx('MONTH',today(),-2,'END'),yymmddn8.),$8.)),1,6);
call symputx(' LastMonth', LastMonth);

run;

%put &LastMonth;
%put &PreviousMonths;

data _NULL_;
monthdiff = intck('MONTH',&PreviousMonths,&LastMonth);
call symputx('monthdiff',monthdiff);
run;

%put &monthdiff;
data _NULL_;

TM = substr(left(input(put(intnx('MONTH',today(),-1,'END'),yymmddn8.),$8.)),1,6);
call symputx('TM',TM);

run;

%put TM : &TM;

%macro Tbl(sub);

libname Tr "C:\Users\siban004\OneDrive - Vodafone Group\Documents\Tranches2\&sub";

proc sql;

create table Collect_&sub as select distinct
Tranche,
VAT
RunMonth as Current_Month,
&dt as Run_Month,
sum(Final_collection_&dt) as Collections,
sum(Final_collection_&dt)* VAT as VAT,
(sum(Final_Collection_&dt) + (sum(Final_Collection_&dt)* VAT))* 0.15/100 as Interest,
sum(Final_Collection_&dt) + ((sum(Final_Collection_&dt) )* VAT) +
(sum(Final_Collection_&dt) + (sum(Final_Collection_&dt)* VAT))* 0.15/100 as Final_Payment,
sum(Final_Collection_&dt) + ((sum(Final_Collection_&dt) )* VAT) as Final_Payment_excl_Interest

from Tr.Final_collections_&TM

quit;

[Kurt_Bremser · Accepted solution]

First of all, you overcomplicate things by a mile, causing non-working code.

Since your macro variables PreviousMonths and LastMonth contain strings with year and month in YYYYMM format, this calculation will not work as intended:

monthdiff = intck('MONTH',&PreviousMonths,&LastMonth);

as it calculates the month difference of a date 202112 days after 1960-01-01 and a date 202204 days after 1960-01-01.

I suggest you first create a list of datasets to process, then create a view from that list where you also keep the month from the dataset name as a new variable, and then run your SQL query with a GROUP BY that incorporates the new variable:

data _null_;
length datasets $500;
do i = -6 to -4;
  mth = put(intnx('month',today(),i),yymmn6.);
  datasets = catx(" ",datasets,"tr.final_collections_"!!mth);
end;
call symputx("datasets",datasets);
run;

data fc_all / view=fc_all;
length dsname $41 month $6;
set &datasets. indsname=dsname;
month = scan(dsname,-1,"_");
run;

proc sql;
create table Collect_&sub as select
  month,
  Tranche,
  VAT,
  &dt as Run_Month,
  RunMonth as Current_Month,
  sum(Final_collection_&dt) as Collections,
  sum(Final_collection_&dt)* VAT as VAT,
  (sum(Final_Collection_&dt) + (sum(Final_Collection_&dt)* VAT))* 0.15/100 as Interest,
  sum(Final_Collection_&dt) + ((sum(Final_Collection_&dt) )* VAT) +
  (sum(Final_Collection_&dt) + (sum(Final_Collection_&dt)* VAT))* 0.15/100 as Final_Payment,
  sum(Final_Collection_&dt) + ((sum(Final_Collection_&dt) )* VAT) as Final_Payment_excl_Interest
  from fc_all
  group by month, Tranche, VAT, calculated Run_month, calculated Current_month
;
quit;

[Nqobile_S14]

Thank you so much for the help, Let me try the solution. I appreciate it.