topic Re: How to do loop from one date to another date? in SAS Programming

How to do loop from one date to another date?

Kayla_Tan222 — Tue, 02 Jul 2019 00:50:29 GMT

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;

Re: How to do loop from one date to another date?

Reeza — Tue, 02 Jul 2019 02:28:29 GMT

Can you please comment your code with what you think is happening and which parts you're trying to change?

For example, this section does nothing at all:

Data pl&yymm..POLA_clm_agg_&yymm.;
set pl&yymm..POLA_clm_agg_&yymm.;

run;

I suggest checking Example 11 from the documentation here:
https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

I would highly recommend a few other changes:

1. Calculate your dates in a data _null_ step, much easier and use actual SAS dates, not numerical variables.
2. Look up CALL EXECUTE to execute the macro for multiple intervals directly from the data _null_ step (Step 1).

Re: How to do loop from one date to another date?

Tom — Tue, 02 Jul 2019 03:42:59 GMT

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?