SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

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

Reply
Established User
Posts: 1

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

[ Edited ]

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

Super User
Posts: 11,343

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

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.

Ask a Question
Discussion stats
  • 1 reply
  • 118 views
  • 0 likes
  • 2 in conversation