topic Re: Using Macro VAriable as Column Name Static Text in PROC SQL in SAS Programming

Using Macro VAriable as Column Name Static Text in PROC SQL

rajesh1980 — Wed, 28 Oct 2020 09:10:25 GMT

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.

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

Amir — Wed, 28 Oct 2020 09:16:59 GMT

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.

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

Kurt_Bremser — Wed, 28 Oct 2020 09:33:36 GMT

Why do you think you need %BQUOTE?

This will work:

select
  RATELIT,
  "&CHARGE_TYPE." as CHARGE_TYPE,
  SUM(RM_BILLED_NETAMOUNT) as CHARGE

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

rajesh1980 — Wed, 28 Oct 2020 17:36:00 GMT

Nn eof the options worked. I finally did this

call symput('CHARGE_TYPE', quote(charge_type,"'"));

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

SASKiwi — Wed, 28 Oct 2020 20:38:14 GMT

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

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

PaigeMiller — Wed, 28 Oct 2020 21:15:05 GMT

@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).

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

rajesh1980 — Thu, 29 Oct 2020 19:51:35 GMT

Sorry for being late on this. Yes i tried both @Kurt_Bremser and @Amir solutions it wasnt still adding the quotes int he SQL. It was showing fine when i displayed it using %put. Hence i resorted to the solution mentioned above. .

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

rajesh1980 — Fri, 06 Nov 2020 04:33:21 GMT

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.

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

Kurt_Bremser — Fri, 06 Nov 2020 06:05:22 GMT

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.

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

rajesh1980 — Fri, 06 Nov 2020 06:53:11 GMT

@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

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

Kurt_Bremser — Fri, 06 Nov 2020 06:57:57 GMT

As I told you earlier, use double quotes and no %BQUOTE, and everything's fine. No need to overcomplicate things.

Re: Using Macro VAriable as Column Name Static Text in PROC SQL

rajesh1980 — Fri, 06 Nov 2020 07:04:01 GMT

That was the most elegant solution ever!!! Thank you very much.