Hi all, facing some problems regarding to have a do loop to loop between date (only the month and year).
Plus can I extract the year and month while looping them?
I have startyear, startmonth, endyear and endmonth for user to set according to their need.
For example I set the startyear and month is 2015 and 6, while the end is 2019 and 2.
Can I loop 6/2015, 7/2015 , 8/2015 ,...................., 1/2019, 2/2019 like this?
The do loop I want to apply is only for the OS which is on the last part in bold.
while the accyrfy and accmthfy in orange text is based on the year and the month in the loop.
my code is below:
%let startyr=2015;
%let startmth=6;
%let endyr=2019;
%let endmth=2;
%let yymm=%eval(&endyr*100+&endmth);
%macro date(startyr, startmth,endyr,endmth);
libname pl&yymm. "\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\DBF";
Data pl&yymm..POLA_clm_agg_&yymm.;
set pl&yymm..POLA_clm_agg_&yymm.;
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;
%if &startmth=12 %then %do;
%let accyr_fy1=%eval(&startyr+1);
%let accmth_fy1=1;
%end;
%else %do;
%let accyr_fy1=&startyr;
%let accmth_fy1=%eval(&startmth+1);
%end;
%if &endmth=12 %then %do;
%let accyr_fy2=%eval(&endyr+1);
%let accmth_fy2=1;
%end;
%else %do;
%let accyr_fy2=&endyr;
%let accmth_fy2=%eval(&endmth+1);
%end;
%let accyrmth_fy1=%eval(&accyr_fy1*100+&accmth_fy1);
%let accyrmth_fy2=%eval(&accyr_fy2*100+&accmth_fy2);
/*****************************OS*****************************/
PROC SQL;
create table SMCD_OS_&yymm. AS
SELECT put(zmajrsk,$class.) as class, min(&accyrfy.-lossyr_fy,11) as dev11yr,accyr_fy,accmth_fy,sum(zclmos) AS zclmos
FROM pl&yymm..POLA_clm_agg_&yymm.
where (accyr_fy*100+accmth_fy)<=(&accyrfy.*100+&ACCMTHFY.)
GROUP BY class,2,3,4
order by accyr_fy,accmth_fy;
quit;
ods html
file="\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\result\SMCD_mth_OS_FY_10DY_&yymm..html"
style=normal;
proc print;
run;
%mend date;
%date(&startyr, &startmth,&endyr,&endmth);
run;
Let's clean up the formatting a little so we can see the code you are trying to run.
So here is your macro definition. I removed the PROC FORMAT so it is not re-created everytime you call the macro. Also removed the empty data step.
%macro date(startyr, startmth,endyr,endmth);
libname pl&yymm. "\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\DBF";
%if &startmth=12 %then %do;
%let accyr_fy1=%eval(&startyr+1);
%let accmth_fy1=1;
%end;
%else %do;
%let accyr_fy1=&startyr;
%let accmth_fy1=%eval(&startmth+1);
%end;
%if &endmth=12 %then %do;
%let accyr_fy2=%eval(&endyr+1);
%let accmth_fy2=1;
%end;
%else %do;
%let accyr_fy2=&endyr;
%let accmth_fy2=%eval(&endmth+1);
%end;
%let accyrmth_fy1=%eval(&accyr_fy1*100+&accmth_fy1);
%let accyrmth_fy2=%eval(&accyr_fy2*100+&accmth_fy2);
PROC SQL;
create table SMCD_OS_&yymm. AS
SELECT put(zmajrsk,$class.) as class
, min(&accyrfy.-lossyr_fy,11) as dev11yr
, accyr_fy
, accmth_fy
, sum(zclmos) AS zclmos
FROM pl&yymm..POLA_clm_agg_&yymm.
where (accyr_fy*100+accmth_fy)<=(&accyrfy.*100+&ACCMTHFY.)
GROUP BY class,2,3,4
order by accyr_fy,accmth_fy
;
quit;
ods html
file="\\kaiwksgh415thw5\Data\POLA\Claim\&endyr\&yymm\result\SMCD_mth_OS_FY_10DY_&yymm..html"
style=normal
;
proc print;
run;
%mend date;
Why do you not close the ODS HTML destination after the PROC PRINT?
Once you have defined your macro then your code is like this:
%let startyr=2015;
%let startmth=6;
%let endyr=2019;
%let endmth=2;
%let yymm=%eval(&endyr*100+&endmth);
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;
%date(&startyr, &startmth,&endyr,&endmth);
Can you explain what it is that you want to loop?
Do you want to call the macro multiple times?
What changes between the calls?
Do you want the start year and start month to go from 2015-06 to 2019-02?
Do you want the end year and end month to stay the same? Do you want them to change also?
How large are the datasets created by the SQL query and printed? Are they 100 observations? 10 Million?
Couldn't just just structure the SQL to generate all of the various sums of ZCLMOS in one query by joining with a table of yr/month values?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.