how to loop my code when i already have %macro in my code

[Kayla_Tan222]

Hi all, I already have a %macro statement so that I only have to enter the beginning year and month, and also end year and end month I want everytime I want to run the file. However, I was facing a problem in the proc sql code. The code is correct but I want it to be inside the loop that depends on the beginning and end date I set. There are 4 different proc sql codes below, I want the code that highlighted in orange to be variable that depends on the date I set.

For Ex, the beg date I set here is 201812 and the end date is 201902,

For the first and second proc sql code,i want it to be run 3 times but different accmth_fy which is 1,2 and 3 (since there is 3 months from 201812 to 201902)

For the third ans forth proc sql code, I want the code to run 3 times .Not only pl201812.POLA_clm_agg_201812 but also (pl201901.POLA_clm_agg_201901) and  (pl201890.POLA_clm_agg_201902). which is 3 months also.

It is impossible for me to list down all the same code for these 4 different proc sql if the beginning date I set is 201812 until the end date which is 201911. that will be total of 44 codes for my 4 different proc sql.

AND, the code must be work for if I only set for one month, if I set the beginning date and end date is the same. For Ex : 201812.

How can I loop for the proc sql since I already have %macro in my code?

%macro date(Beg_YYYY=2018, Beg_MM=12,End_YYYY=2019,End_MM=02,Val_YYYY=2019,Val_MM=02);%global accYR accMTH;

%if &End_MM = 12 %then %do;

%let accYR=%eval (End_YYYY+1)

%let accMTH = %eval(&End_MM + 1);

%end;

　

%else %do;

%let accYR = &End_YYYY;

%let accMTH = %eval(&End_MM+1);

%end;

　

　

libname pl&Val_YYYY&MM. "\\kaiwksgh415thw5\Data\POLA\Claim\&Val_YYYY\&Val_YYYY&MM\DBF";

Data pl&Val_YYYY&MM..POLA_clm_month_&Val_YYYY&MM.;

set pl&Val_YYYY&MM..POLA_clm_agg_&Val_YYYY&MM.;

where accyr_fy=&accYR. & accmth_fy<=&accMTH.; /***financial basis***/

dev11yr_fy=min(accyr_fy-lossyr_fy,11);

run;

PROC FORMAT;

value $ class

'VEH'='1'

'FIR'='2'

'GMS'='3'

'BON'='3'

'HUL'='3'

'PAC'='4'

'TEL'='5'

'WWC'='5'

'LIA'='6'

'CGO'='7'

'ENG'='8'

;

RUN;

/************************************************************/

PROC SQL;

create table SMCD_PD AS

SELECT put(zmajrsk,$class.) as class, dev11yr_fy,sum(zclmpd) AS zclmpd

FROM pl201902.POLA_clm_month_201902

WHERE accmth_fy=1 /****FINANCIAL BASIS*****/

GROUP BY class,2;

quit;

ods html

file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_PD_Jan_10DY_201902.html"

/********************************************************************/

　

PROC SQL;

create table SMCD_PDNET AS

SELECT put(zmajrsk,$class.) as class, dev11yr_fy,sum(zclmpdnet) AS zclmpdnet

FROM pl201902.POLA_clm_month_201902

WHERE accmth_fy=1

GROUP BY class,2;

quit;

ods html

file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_PDNET_month_FY_10DY_201902.html"

style=normal;

proc print;

run;

　

/****************************************************************************/

　

　

libname pl201812 "\\kaiwksgh415thw5\Data\POLA\Claim\2018\201812\DBF";

Data pl201812.POLA_clm_agg_201812;

set pl201812.POLA_clm_agg_201812;

run;

　

PROC SQL;

create table SMCD_OS AS

SELECT put(zmajrsk,$class.) as class, min(2019-lossyr_fy,11) as devyr,sum(zclmos) AS zclmos

FROM pl201812.POLA_clm_agg_201812

GROUP BY class,2;

quit;

ods html

file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_OS_month_FY_10DY_201902.html"

style=normal;

proc print;

run;

　

　

　

　

　

　

　

/*****************************************************************/

PROC SQL;

create table SMCD_OSNET AS

SELECT put(zmajrsk,$class.) as class, min(2019-lossyr_fy,11) as devyr,sum(zclmosnet) AS zclmosnet

FROM pl201812.POLA_clm_agg_201812

GROUP BY class,2;

quit;

ods html

file="\\kaiwksgh415thw5\Data\POLA\Claim\2018\201812\result\SMCD_OSNET_month_FY_10DY_201812.html"

style=normal;

proc print;

run;

　

　

　

%mend date;

%date(Beg_YYYY=2018, Beg_MM=12,End_YYYY=2019,End_MM=02,Val_YYYY=2019,Val_MM=.2);

run;

[Tom]

It is not completely clear what you are asking.

A %MACRO statement just starts the definition of a macro.  So I am a not sure how your comment about having a macro in your code interacts with the question about what you want to do.

If you want to build a piece of code where part if varies then a good way to set that part into a macro variable and then replace that part with the value of the macro variable.   For example it looks a lot like you want the dataset names that your code uses to have a suffix that reflect a month value.

POLA_clm_agg_201812 

So one way to do that is to set that part of the name into a macro variable.

%let month=201812;

And then replace the constant string with the reference to the macro variable.

POLA_clm_agg_&month.

Now if you want to generate a loop that does the same thing for three months in a row then something like this should help. 

%let start_date = '01DEC2018'd;
%do offset=0 %to 2 ;
   %let month=%sysfunc(intnx(month,&start_date,&offset),yymmn6.);
   .... rest of code that uses &MONTH ....
%end;

Since you appear to already be defining a macro you might be able to include these macro flow control statements into that macro.  Or define another macro that does the loop and calls the original macro with the appropriate input values.

[Kayla_Tan222]

Hi Tom, what I meant is something like this below, the code between /*******/ have to loop 3 times with different number which is 1 to 3.

However the code below still not work.

　

　

libname pl201902 "\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\DBF";

Data pl201902.POLA_clm_month_201902;

set pl201902.POLA_clm_agg_201902;

where accyr_fy=2019 & accmth_fy<=3; /***financial basis***/

dev11yr_fy=min(accyr_fy-lossyr_fy,11);

run;

PROC FORMAT;

value $ class

'VEH'='1'

'FIR'='2'

'GMS'='3'

'BON'='3'

'HUL'='3'

'PAC'='4'

'TEL'='5'

'WWC'='5'

'LIA'='6'

'CGO'='7'

'ENG'='8'

;

RUN;

%do i=1 %to 3;

%let accmth=i;

　

/************************************************************/

PROC SQL;

create table SMCD_PD AS

SELECT put(zmajrsk,$class.) as class, dev11yr_fy,sum(zclmpd) AS zclmpd

FROM pl201902.POLA_clm_month_201902

WHERE accmth_fy=&accmth /****FINANCIAL BASIS*****/

GROUP BY class,2;

quit;

ods html

file="\\kaiwksgh415thw5\Data\POLA\Claim\2019\201902\result\SMCD_PD_Jan_10DY_201902.html"

style=normal;

proc print;

run;

　

/************************************************************/

%end;

run;

[heffo]

%let accmth=i; should be %let accmth=&i;
As it is now, the macrovariable &accmth contains the letter i, not the number that the macro variable i contains.

[heffo]

Sort of what @Tom started with. This code takes the start date and end date and loops the number of months there is between the dates. In each loop you will have to add the four SQL queries with the correct month and year.

%macro onePass(Year, Month);
	*Do one of the passes of your four SQL;
	*No code for easier reading of a solution;
	%if &month < 10 %then %let month=0&month; *Make sure you have a leading 0 on the month;
	%put &=year &=month; * Use &year and &month in your code instead of the static years and month.
%mend onePass;

%macro dateLoop(Beg_YYYY=2018, Beg_MM=12,End_YYYY=2019,End_MM=02,Val_YYYY=2019,Val_MM=02);
	%let startDate = %sysfunc(mdy(&Beg_MM,01,&Beg_YYYY));
	%let endDate = %sysfunc(mdy(&End_MM,01,&End_YYYY));
	%let NumberOfMonths = %sysfunc(intck(month,&startDate,&endDate));
	%do _i=1 %to &NumberOfMonths +1; *We have to add one month.;
		%let date=%sysfunc(intnx(month,&startDate,&_i));
		%let month =%sysfunc(month(&date));
		%let year =%sysfunc(year(&date));
		%onePass(&year, &month);
	%end;
%mend dateLoop;

%dateLoop(Beg_YYYY=2018, Beg_MM=12,End_YYYY=2018,End_MM=12,Val_YYYY=2019,Val_MM=02);

[ScottBass]

https://github.com/scottbass/SAS/blob/master/Macro/loop.sas or

https://github.com/scottbass/SAS/blob/master/Macro/loop_control.sas

See macro header for usage examples.