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;
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.
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;
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);
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.