Inserting macro variable values

[Swathi12]

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?

[PaigeMiller]

Show us the LOG. Click on the {i} icon and paste the log into the window that appears. DO NOT SKIP THIS STEP.

[Astounding]

One known issue: SQL doesn't resolve %str(%') in time. After assigning a value to &month_key, you can address the problem by adding

%let month_key = %unquote(&month_key);

[ErikLund_Jensen]

Hi @Swathi12 

As addition to what @Astounding replied: I think you have a problem with %let sequencenum=%str(%0)%str(%0)&seq_num_new;

What is written to your log from the statement %put &=batchnum;? - if it is something like BATCHNUM=20190219%0%0124, this would give you trouble too. It might be easier to skip all the macro work and calculate/generate the macro variables batchnum and month_key within the data _null_ step.

[Tom]

Looks like you need to learn how to use formats for generating the strings.

You can use the YYMMDDN format to convert a date into an eight character string of digits representing year, month, and day.

You can use the Z format to convert a number into a string of digits with leading zeros.

Since the macro is already running procs and steps there is no need to try to generate the macro variable value with pure macro code. It is easier to manipulate data using a data step.

So your program might look like:

%macro PY_Tiercalc_Staging(Mth);
%local month_key batchid batchnum ;

proc sql noprint ;
  select max(BatchId) into :BatchId 
    from PYODS.PY_TierCalcStaging
    where datepart(lastupdatedate)= today()
  ;
quit;
data _null_;
  call symputx('month_key',"'&mtn'");
  call symputx('batchnum',cats(put(today(),yymmddn8.),put(&batchid,z3.)));
run;

proc sql;
  insert into PYODS.PY_TierCalcStaging (BatchId,ciskey,calcyearmonth,.....)
  select &batchnum.,ciskey,&month_key.,.....
  from MYWRLDPY.py_tiercalcstaging
;
quit;
%mend;