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
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.