automatically itirate from endmonth data to prior eleven months data without resetting dates

so1gban — Thu, 25 May 2017 17:21:55 GMT

I have the following SAS macro to regenerate monthly credit exceptions balances. to avoid date errors, I do not want to constantly change dates. I need to automate the process with a macro. Here is my current process.

****** UPDATE MACRO DATES FOR LAST 12 MONTHS ****************;
%let Mth4 = 01;
%let Yr4 = 2017;

%macro dates(date, mth, yr);
    proc sql ;
        /* connect to oracle as db(user=&usr. password=&pass. path=f3rdprod buffsize=4000)*/;
        create table get_data as /*select * from connection to db*/(select acct_num, 
            acct_key, app_num, bank_num, dealr_loc, amt_orig_loan, amt_principal_bal, 
            dt_loan_entry from emrsstap.dr_plr_&date /*plr_accnt_dtl*/
            where proc_type='05' and acct_purge_ind='N' and collat_cd='AUT' and 
            mth_key=&date.);
    quit;

    data get_losses;
        set get_data;
        where collat_purp_cd ^='BUS' and (amt_coff_mtd ^=0 or amt_recover_mtd ^=0);
        amt_ncoff_mtd=amt_coff_mtd - amt_recover_mtd;
    run;

    data get_orig;
        set get_data;
        where collat_purp_cd ^='BUS' and datepart(dt_loan_entry) >=mdy(&mth, 01, &yr);
        accountnumber=acct_num * 1;
    run;

    proc summary data=get_data;
        where collat_purp_cd ^='BUS' and acct_stat ^='CL' and proc_stat ^='CF';
        class bank_num dealr_loc;
        var amt_principal_bal;
        output out=get_balances sum=;
    run;

    data get_balances;
        set get_balances;
        mth_bal=&date;
    run;

    proc append data=get_losses base=Losses force;
    run;

    proc append data=get_EPD base=EPD3 force;
    run;

    proc append data=get_orig base=Orig force;
    run;

    proc append data=get_balances base=running_Balances force;
        where _TYPE_=3;
    run;

    proc append data=get_early_payoff base=Early_Payoff force;
    run;

%mend;

%dates(201505, 05, 2015);
%dates(201506, 06, 2015);
%dates(201507, 07, 2015);
%dates(201508, 08, 2015);
%dates(201509, 09, 2015);
%dates(201510, 10, 2015);
%dates(201511, 11, 2015);
%dates(201512, 12, 2015);
%dates(201601, 01, 2016);
%dates(201602, 02, 2016);
%dates(201603, 03, 2016);
%dates(201604, 04, 2016);

data exceptions;
    set consind.exceptions_2016;
    where mdy(&mth12, 01, &yr12) <=pbookingdate < mdy(&mth12, 01, &yr);
    excep=1;
run;

=====================I am working to automate with this process but keep
getting errors.

data _null_;
    call symput('MthKeyCurrent', "'" put(&EndMonth, yymmn6.));
    call symput('MthKey1Prior', "'" put(intnx('month', &EndMonth, -1, 'e'), 
        yymmn6.));
    call symput('MthKey2Prior', "'" put(intnx('month', &EndMonth, -2, 'e'), 
        yymmn6.));
    call symput('PMADDate', "'"||put(&EndMonth, mmddyy10.)||"'");
    call symput('DWDate', "'"||put(&EndMonth, yymmdd10.)||"'");
run;

%put &MthKeyCurrent &MthKey1Prior &MthKey2Prior &PMADDate &DWDate;

can someone help?

St. Sypress

Re: automatically itirate from endmonth data to prior eleven months data without resetting dates

ballardw — Thu, 25 May 2017 16:33:56 GMT

Post the log with the code an results of

data _null_;

call symput('MthKeyCurrent', "'" put(&EndMonth,yymmn6.));

call symput('MthKey1Prior', "'" put(intnx('month',&EndMonth,-1,'e'),yymmn6.));

call symput('MthKey2Prior', "'" put(intnx('month',&EndMonth,-2,'e'),yymmn6.));

 

call symput('PMADDate', "'"||put(&EndMonth,mmddyy10.)||"'");

call symput('DWDate', "'"||put(&EndMonth,yymmdd10.)||"'");

run;

%put &MthKeyCurrent &MthKey1Prior &MthKey2Prior &PMADDate &DWDate;

 

can someone help?

Also paste it into a codebox opened with the forum {i} menu icon to preserve formating of the error messages.

it will also help to provide the values you had for &endmonth.

topic Re: automatically itirate from endmonth data to prior eleven months data without resetting dates in SAS Data Management

automatically itirate from endmonth data to prior eleven months data without resetting dates

Re: automatically itirate from endmonth data to prior eleven months data without resetting dates