Hi, PROC SQL; INSERT INTO PYODS.PY_TierCalcStaging (BatchId,ciskey,calcyearmonth,firstname,CustomerStatus,Action,tierid,Tenure,OnlineRegisteredFlag,StaffIndicator,VIPIndicator,TotalCalcPoints,CalcTenureFlag,ProdTranAcctFlag, ProdSavingAcctFlag,ProdTermDepositFlag,ProdCreditCardFlag,ProdPersonalLoanFlag,ProdOwnerOccHLFlag,ProdInvestorHLFlag,ProdGeneralInsFlag,ProdSuperFlag,ProdInvestmentFlag,ProdPersonalInsFlag,BehaviourFlagTranFlag, BehaviourBanksOnlineFlag,LoadStatus) SELECT 20190201001, ciskey,'201812',firstname,CustomerStatus,Action,tierid,Tenure,OnlineRegisteredFlag,StaffIndicator,VIPIndicator,TotalCalcPoints,CalcTenureFlag,ProdTranAcctFlag, ProdSavingAcctFlag,ProdTermDepositFlag,ProdCreditCardFlag,ProdPersonalLoanFlag,ProdOwnerOccHLFlag,ProdInvestorHLFlag,ProdGeneralInsFlag,ProdSuperFlag,ProdInvestmentFlag,ProdPersonalInsFlag,BehaviourFlagTranFlag, BehaviourBanksOnlineFlag,LoadStatus FROM MYWRLDPY.py_tiercalcstaging; QUIT; is working fine. If I replace the numerics with the macro variable like below: LIBNAME PYODS SQLSVR DSN=*************** USER='********' PASSWORD='********'; %macro PY_Tiercalc_Staging(Mth); %let month_key = %str(%')%sysfunc(putn(&mth,6.))%str(%'); PROC SQL; create table sequence_number as select Max(BatchId) as Max_BatchId from PYODS.PY_TierCalcStaging where datepart(lastupdatedate)= today(); QUIT; data _null_; set sequence_number; call symput('Seqnum',Max_batchid); run; %put &=seqnum; %if &seqnum=. %then %do; %let sequencenum=001; %end; %else %do; %let Seq_num=%substr(&Seqnum,9,3); %let seq_num_new=%eval(&seq_num+1); %let sequencenum=%str(%0)%str(%0)&seq_num_new; %end; %if %length(%sysfunc(month("&sysdate"d))) < 2 %then %do; %let thismonth=0%sysfunc(month("&sysdate"d)); %end; %else %do; %let thismonth=%sysfunc(month("&sysdate"d)); %end; %put &=thismonth; %if %length(%sysfunc(day("&sysdate"d))) < 2 %then %do; %let thisday=0%sysfunc(day("&sysdate"d)); %end; %else %do; %let thisday=%sysfunc(day("&sysdate"d)); %end; %put &=thisday; %let batchnum=%sysfunc(year("&sysdate"d))&thismonth.&thisday.&sequencenum.; %put &=sequencenum; %put &=batchnum; PROC SQL; INSERT INTO PYODS.PY_TierCalcStaging (BatchId,ciskey,calcyearmonth,firstname,CustomerStatus,Action,tierid,Tenure,OnlineRegisteredFlag,StaffIndicator,VIPIndicator,TotalCalcPoints,CalcTenureFlag,ProdTranAcctFlag, ProdSavingAcctFlag,ProdTermDepositFlag,ProdCreditCardFlag,ProdPersonalLoanFlag,ProdOwnerOccHLFlag,ProdInvestorHLFlag,ProdGeneralInsFlag,ProdSuperFlag,ProdInvestmentFlag,ProdPersonalInsFlag,BehaviourFlagTranFlag, BehaviourBanksOnlineFlag,LoadStatus) SELECT &batchnum., ciskey,&month_key.,firstname,CustomerStatus,Action,tierid,Tenure,OnlineRegisteredFlag,StaffIndicator,VIPIndicator,TotalCalcPoints,CalcTenureFlag,ProdTranAcctFlag, ProdSavingAcctFlag,ProdTermDepositFlag,ProdCreditCardFlag,ProdPersonalLoanFlag,ProdOwnerOccHLFlag,ProdInvestorHLFlag,ProdGeneralInsFlag,ProdSuperFlag,ProdInvestmentFlag,ProdPersonalInsFlag,BehaviourFlagTranFlag, BehaviourBanksOnlineFlag,LoadStatus FROM MYWRLDPY.py_tiercalcstaging; QUIT; %mend; I get the below error: ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, BTRIM, CALCULATED, CASE, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, USER, ^, ~. ERROR 200-322: The symbol is not recognized and will be ignored. what's wrong with the code?
... View more
