Hello Everyone,
Im setting a macro variable with a text value . Later im using this macro variable as a static string value in a SELECT statement in a PROC SQL while inserting rows. i using bquote to add single quotes to the static string but however i'm getting the error . What am i doing wrong ?
25 %let CHARGE_TYPE = Generation (Post); 26 %let WHERE_CLAUSE = CASUBTRANS in ('1130', '1131'); 27 28 proc sql; 29 insert into BASE_UNBUND_REV_MONTHLY 30 31 select RATELIT, 22: LINE and COLUMN cannot be determined. ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT, FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=. 200: LINE and COLUMN cannot be determined. ERROR 200-322: The symbol is not recognized and will be ignored. 32 %bquote('&CHARGE_TYPE.') as CHARGE_TYPE, NOTE: Line generated by the macro function "BQUOTE". 32 'Generation (Post)' _ 22 _ 200 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. 33 SUM(RM_BILLED_NETAMOUNT) as CHARGE 34 from BILLDOC_MOD_MONTHLY 35 where &WHERE_CLAUSE. 36 group by RATELIT 37 ;
%let CHARGE_TYPE = Generation (Post); %let WHERE_CLAUSE = CASUBTRANS in ('1130', '1131'); proc sql; insert into BASE_UNBUND_REV_MONTHLY select RATELIT, %bquote('&CHARGE_TYPE.') as CHARGE_TYPE, SUM(RM_BILLED_NETAMOUNT) as CHARGE from BILLDOC_MOD_MONTHLY where &WHERE_CLAUSE. group by RATELIT ; run;
This is my code.
As I told you earlier, use double quotes and no %BQUOTE, and everything's fine. No need to overcomplicate things.
Hi @rajesh1980 ,
Have you tried using double quotes instead of single quotes in:
'&CHARGE_TYPE.'
Macro variables do not resolve when using single quotes.
Kind regards,
Amir.
Why do you think you need %BQUOTE?
This will work:
select
RATELIT,
"&CHARGE_TYPE." as CHARGE_TYPE,
SUM(RM_BILLED_NETAMOUNT) as CHARGE
Nn eof the options worked. I finally did this
call symput('CHARGE_TYPE', quote(charge_type,"'"));
This also works ( I assume you need single quotes as you are updating an external database):
select
RATELIT,
%str(%')&CHARGE_TYPE.%str(%') as CHARGE_TYPE,
SUM(RM_BILLED_NETAMOUNT) as CHARGE
@rajesh1980 wrote:
Nn eof the options worked. I finally did this
call symput('CHARGE_TYPE', quote(charge_type,"'"));
It's unclear why you say this. Both the solution from @Kurt_Bremser and @Amir will work, and are quite a bit simpler than CALL SYMPUT with the QUOTE() function (and using double quotes is better solution from a code readability and code maintainability point of view as well).
i can show you the problem im facing
This is the code im running, i have created a macro SCE_OOR_REV_BILLING_FINANCE and it has PROC SQL statement with the macro Argument &CHARGE as a column name as well as text field. I used BQUOTE to add quotes to the macro variable but i am getting the following error.
%macro SCE_OOR_REV_BILLING_FINANCE(CHARGE=); proc sql; create table SCE_OOR_REV_BILLING_FINANCE as select CONTRACT, %bquote('&CHARGE') as CHARGE_DESC, sum(CHARGE.) as CHARGE from ( select contract, 0 as LATE_PaAYMENT_CHARGE_COMM, 0 as LATE_PaAYMENT_CHARGE_RESIDENTIAL, 0 as RETURNED_CHECK_CHARGES, 0 as SERVICE_CONNECTION_CHARGES_RES, 0 as SERVICE_CONN_CHARGES_NON_RES, 0 as SERVICE_CONN_CHARGES_AT_POLE, 0 as OPTOUT_RESCARE_INIT_FEE, 0 as OPTOUT_FERA_NON_CARE_IFEE, 0 as RECOVER_UNAUTH_USE_ENERGY, 0 as SERVICE_FEE_OPTIMAL_BILL, 0 as AR_SERVICE_GUARANTEE, 0 as CCA_INFORMATION_FEES, 0 as DA_REVENUE, 0 as DMS_SERVICE_FEE, OPT_OUT_RESCARE_MONTHLY, OPT_OUT_FERA_NONCARE_MONTHLY, ELECTRIC_SURCHARGE, SCE_ENERGY_MANAGER_FEE, CUST_FIN_ADDED_FAC_FERC, CUST_FIN_ADDED_FAC_CPUC, SCE_FIN_ADDED_FAC_FERC, SCE_FIN_ADDED_FAC_CPUC, CUST_FIN_IC_ADD_FAC_FERC, CUST_FIN_IC_ADD_FAC_CPUC, SCE_FIN_IC_ADD_FAC_FERC , SCE_FIN_IC_ADD_FAC_CPUC from SCE_OOR_REV_BILLING union all select contract, LATE_PaAYMENT_CHARGE_COMM, LATE_PaAYMENT_CHARGE_RESIDENTIAL, RETURNED_CHECK_CHARGES, SERVICE_CONNECTION_CHARGES_RES, SERVICE_CONN_CHARGES_NON_RES, SERVICE_CONN_CHARGES_AT_POLE, OPTOUT_RESCARE_INIT_FEE , OPTOUT_FERA_NON_CARE_IFEE, RECOVER_UNAUTH_USE_ENERGY, SERVICE_FEE_OPTIMAL_BILL, AR_SERVICE_GUARANTEE, CCA_INFORMATION_FEES, DA_REVENUE, DMS_SERVICE_FEE, 0 as OPT_OUT_RESCARE_MONTHLY, 0 as OPT_OUT_FERA_NONCARE_MONTHLY, 0 as ELECTRIC_SURCHARGE, 0 as SCE_ENERGY_MANAGER_FEE, 0 as CUST_FIN_ADDED_FAC_FERC, 0 as CUST_FIN_ADDED_FAC_CPUC, 0 as SCE_FIN_ADDED_FAC_FERC, 0 as SCE_FIN_ADDED_FAC_CPUC, 0 as CUST_FIN_IC_ADD_FAC_FERC, 0 as CUST_FIN_IC_ADD_FAC_CPUC, 0 as SCE_FIN_IC_ADD_FAC_FERC, 0 as SCE_FIN_IC_ADD_FAC_CPUC FROM SCE_OOR_REV ) group by CONTRCAT ; run; %mend SCE_OOR_REV_BILLING_FINANCE; %SCE_OOR_REV_BILLING_FINANCE(CHARGE=LATE_PaAYMENT_CHARGE_COMM);
99 %SCE_OOR_REV_BILLING_FINANCE(CHARGE=LATE_PaAYMENT_CHARGE_COMM); NOTE: Line generated by the invoked macro "SCE_OOR_REV_BILLING_FINANCE". 99 create table SCE_OOR_REV_BILLING_FINANCE as select CONTRACT, %bquote('&CHARGE') as CHARGE_DESC, 99 ! sum(CHARGE.) as CHARGE from ( select contract, 0 as LATE_PaAYMENT_CHARGE_COMM, _ 22 99 ! 0 as ERROR 22-322: Syntax error, expecting one of the following: a name, *. NOTE: Line generated by the macro function "BQUOTE". 99 'LATE_PaAYMENT_CHARGE_COMM' _ 22 _ 200 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, ^, ~. 3 The SAS System Thursday, November 5, 2020 06:05:00 PM ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
What makes it confusing is this
NOTE: Line generated by the macro function "BQUOTE".
99 'LATE_PaAYMENT_CHARGE_COMM'
So it is resolving properly but somehow PROC SQL is failing.
SAS points you directly to your mistake, which is not in the %BQUOTE, but here:
sum(CHARGE.)
CHARGE. is not a valid column name in SQL. If CHARGE was a defined table alias or table name, then the name of a column from that table has to follow after the dot.
@Kurt_Bremser Sir, yeah i noticed that one too after i posted this, buit fixing that also did not resolve my issue. Still i am getting the same error. To test it out i wrote a small sample program which reproduces the error. If you can help me debug it, that would be great
Program
%macro test_macro(var=); proc sql; create table test as select %bquote('&var') as COLUMN1 from sashelp.class ; run; %mend test_macro; %test_macro(var=ABCD);
Output
1 The SAS System Thursday, November 5, 2020 09:02:00 PM 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROCESSFLOWNAME='Process Flow'; 5 %LET _CLIENTPROJECTPATH=''; 6 %LET _CLIENTPROJECTPATHHOST=''; 7 %LET _CLIENTPROJECTNAME=''; 8 %LET _SASPROGRAMFILE=''; 9 %LET _SASPROGRAMFILEHOST=''; 10 11 ODS _ALL_ CLOSE; 12 OPTIONS DEV=PNG; 13 FILENAME EGSR TEMP; 14 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 15 STYLE=HTMLBlue 16 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HTMLBlue.css") 17 NOGTITLE 18 NOGFOOTNOTE 19 GPATH=&sasworklocation 20 ENCODING=UTF8 21 options(rolap="on") 22 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 23 24 %macro test_macro(var=); 25 proc sql; 26 create table test as 27 select %bquote('&var') as COLUMN1 from sashelp.class 28 ; 29 run; 30 %mend test_macro; 31 32 %test_macro(var=ABCD); NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. NOTE: Line generated by the macro function "BQUOTE". 32 'ABCD' _ 22 _ 200 NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, (, *, +, -, ALL, BTRIM, CALCULATED, CASE, DISTINCT, EXISTS, INPUT, NOT, PUT, SUBSTRING, TRANSLATE, UNIQUE, USER, ^, ~. ERROR 200-322: The symbol is not recognized and will be ignored. 33 34 %LET _CLIENTTASKLABEL=; 35 %LET _CLIENTPROCESSFLOWNAME=; 36 %LET _CLIENTPROJECTPATH=; 37 %LET _CLIENTPROJECTPATHHOST=; 38 %LET _CLIENTPROJECTNAME=; 39 %LET _SASPROGRAMFILE=; 40 %LET _SASPROGRAMFILEHOST=; 41 42 ;*';*";*/;quit; NOTE: The SAS System stopped processing this step because of errors. 2 The SAS System Thursday, November 5, 2020 09:02:00 PM NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 42 ! run; 43 ODS _ALL_ CLOSE; 44 45 46 QUIT; RUN; 47
As I told you earlier, use double quotes and no %BQUOTE, and everything's fine. No need to overcomplicate things.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.