topic Re: Character Value in Macro variable in SAS Procedures

Character Value in Macro variable

Anuashla — Fri, 04 Dec 2015 21:23:10 GMT

Hello,

With the code below, I am trying to filter the rows in a table where ESP01.SCAT_SALES_CATG2_C has 'PREM' (macro variable Plan_type is resolving correctly to 'PREM') values. But instead, program is considering 'PREM' as the name of a column and giving me an error as pasted below. The way I want the statement to resolve is

ESP01.SCAT_SALES_CATG2_C EQ 'PREM'.

Please suggest where I am making a mistake.

Code:

%macro datacall;
%do i=1 %to 160;
proc sql;
select start_year into: year from splits_grid1 where ID=&i;
select plan_type into: plan_type from splits_grid1 where ID=&i;
select contract_type into: contract_type from splits_grid1 where ID=&i;
quit;
%data (CNTRCT_START_Y, ESP01.SCAT_SALES_CATG2_C EQ "&PLAN_TYPE" and EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y)=&YEAR AND
ESP01.PLTYP_CNTCT_TYPE_C EQ "&CONTRACT_TYPE" and ESP01.COUNTRY_ISO3_C EQ 'CAN', &YEAR&PLAN_TYPE&CONTRACT_TYPE);
%end;
%mend datacall;
%datacall;

LOG Details:

MLOGIC(DATA): Beginning execution.
SYMBOLGEN: Macro variable PLAN_TYPE resolves to PREM
SYMBOLGEN: Macro variable YEAR resolves to 2000
SYMBOLGEN: Macro variable CONTRACT_TYPE resolves to N
SYMBOLGEN: Macro variable YEAR resolves to 2000
SYMBOLGEN: Macro variable PLAN_TYPE resolves to PREM
SYMBOLGEN: Macro variable CONTRACT_TYPE resolves to N
MLOGIC(DATA): Parameter START_DATE has value CNTRCT_START_Y
MLOGIC(DATA): Parameter WHERE has value ESP01.SCAT_SALES_CATG2_C EQ "PREM" and EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y)= 2000
AND ESP01.PLTYP_CNTCT_TYPE_C EQ "N" and ESP01.COUNTRY_ISO3_C EQ 'CAN'
MLOGIC(DATA): Parameter OUTDATA has value 2000PREMN
SYMBOLGEN: Macro variable START_DATE resolves to CNTRCT_START_Y
SYMBOLGEN: Macro variable WHERE resolves to ESP01.SCAT_SALES_CATG2_C EQ "PREM" and EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y)=
2000 AND ESP01.PLTYP_CNTCT_TYPE_C EQ "N" and ESP01.COUNTRY_ISO3_C EQ 'CAN'

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database] Column PREM not found in ESPARS_View.ESP01.

Re: Character Value in Macro variable

Reeza — Fri, 04 Dec 2015 21:52:29 GMT

Can you post the code from the %data() macro? I think the error is in there somewhere instead of what you've posted.

I would expect an error of the form mostly likely seeing &PLANTYPE macro without the quotes.

Though @ballardw may have hit the nail on the head with the macro variable, if any of them had trailing spaces that wouldn't resolve properly.

Re: Character Value in Macro variable

ballardw — Fri, 04 Dec 2015 21:41:53 GMT

I'm going to suggest that MPRINT would be more helpful than symbolgen and mlogic here.

You don't give any details of the macro data so how you are using your variable is pretty much a mystery.

We don't know which positional variable receives the value &YEAR&PLAN_TYPE&CONTRACT_TYPE .

I would check what that string resolves to as I suspect you may have wanted something like &YEAR.&PLAN_TYPE.&CONTRACT_TYPE where the . are the macro separator for combining values.

Re: Character Value in Macro variable

Anuashla — Fri, 04 Dec 2015 22:12:25 GMT

Hello Reeza,

Here is the code for the macro Data

%macro data (start_date, where, outdata);

PROC SQL;
CONNECT TO ODBC (USER=xxxxxxxxx PW=xxxxxxxxx DSN=xxxxxxx);
CREATE TABLE DATA1 AS
SELECT * FROM CONNECTION TO ODBC
(
SELECT DISTINCT
(ESP01.CNTRCT_CONTRACT_R||ESP01.VEH_VIN_C) AS CONTRACT_VIN,
EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y) AS START_YEAR,
CAST ( (ESP01.&START_DATE (FORMAT 'YYYYMM') ) as CHAR(6) ) as START_DATE, /*Contract start time for new vehicle*/
ESP01.PLAN_CNT_CVG_TME_R as term
FROM
ESPARS_View.SSEPR01 as ESP01
WHERE
ESP01.CNTRCT_TEST_MKT_F = 'N' /* necessary constraint to get rid of the free contracts*/
AND &where /*Macro variable for all conditions to filter the contracts*/
AND ESP01.PLBRN_PRDCT_LINE_C IN ('C', 'E', 'F')/* necessary constraint to pull the right contracts*/
order by 1,2

);
DISCONNECT FROM ODBC;
QUIT;

Re: Character Value in Macro variable

Anuashla — Fri, 04 Dec 2015 22:00:20 GMT

Hello,

I am not bothered as much about how &YEAR&PLAN_TYPE&CONTRACT_TYPE resolves. This is just a dataset name and I can chang eit anytime. I am more concerned about how the where clause variables are getting resolved in the macro. I have pasted the code for macro. I believe it should provide information as to which positional variable is taking whihc value.

P.S. &YEAR&PLAN_TYPE&CONTRACT_TYPE is resolving correctly and giving me result as I expected. Thank you for helping though. I would try turning MPRINT option on and see if it helps me resolve the issue.

Re: Character Value in Macro variable

Reeza — Fri, 04 Dec 2015 22:07:41 GMT

You don't actually use the OUTDATA macro variable anywhere in your posted code....so the resolution of the macro variable there doesn't matter. Is that the full code?

Re: Character Value in Macro variable

Anuashla — Fri, 04 Dec 2015 22:14:45 GMT

The complete code is too long but is repition of the codes that I have shared. If you check the log, macro variable Plan_type gets resolved to the correct value which is 'PREM', its only when it appears in the where statement, SAS somehow is considering it as column name instead of a value in the column.

Re: Character Value in Macro variable

Anuashla — Fri, 04 Dec 2015 22:16:58 GMT

This is the [art where I am using Outdata macro variable. It is to create different datasets which I will need to append in the end. I guess this has nothign to do with my issue here???

DATA DATA_&OUTDATA;
MERGE CONTRACTS_DETAILS (IN=A)
CLAIMS1 (IN=B);
BY CONTRACT_VIN EXPOSURE;
IF A AND NOT B THEN CLAIM=0;
IF A AND B THEN CLAIM=1;
IF A;
RUN;

Re: Character Value in Macro variable

Patrick — Fri, 04 Dec 2015 22:40:26 GMT

@Anuashla

Given the messages in the log I believe the issue is caused by the double quotes around PREM. You need to use single quotes.

SYMBOLGEN: Macro variable WHERE resolves to ESP01.SCAT_SALES_CATG2_C EQ "PREM"

ERROR: CLI prepare error: [Teradata][ODBC Teradata Driver][Teradata Database] Column PREM not found

In Teradata SQL double quotes are used for quoting names (eg. a column name) not strings. If using double quotes Teradata expects "PREM" to be an existing column name. That's why you get the error " Column PREM not found".

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1141_111A/ch01.103.19.html

Re: Character Value in Macro variable

Anuashla — Fri, 04 Dec 2015 22:40:41 GMT

Hello Patrick,

I tried using single quote. Still no success. Here is the log for that

MLOGIC(DATA): Beginning execution.
SYMBOLGEN: Macro variable YEAR resolves to 2000
SYMBOLGEN: Macro variable YEAR resolves to 2000
SYMBOLGEN: Macro variable PLAN_TYPE resolves to PREM
SYMBOLGEN: Macro variable CONTRACT_TYPE resolves to N
MLOGIC(DATA): Parameter START_DATE has value CNTRCT_START_Y
MLOGIC(DATA): Parameter WHERE has value ESP01.SCAT_SALES_CATG2_C EQ '&PLAN_TYPE' and EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y)=
2000 AND ESP01.PLTYP_CNTCT_TYPE_C EQ '&CONTRACT_TYPE' and ESP01.COUNTRY_ISO3_C EQ 'CAN'
MLOGIC(DATA): Parameter OUTDATA has value 2000PREMN

Re: Character Value in Macro variable

Patrick — Fri, 04 Dec 2015 22:51:07 GMT

@Anuashla

It's not working because you need to code in a way that macro variable &contract_type resolves before you wrap it into single quotes.

Please post the code snippet where you construct/populate macro variable &where

You need to populate &where following an approach as below:

%let string=Alfred;
%let where= name = %unquote(%nrbquote(')&string%nrbquote('));

proc sql;
  select *
  from sashelp.class
  where &where
  ;
quit;

Re: Character Value in Macro variable

hbi — Fri, 04 Dec 2015 23:27:21 GMT

Since you are using an "explicit" rather than "implicit" pass-through SQL statement, your double quotes are being interpreted as a quoted identifiers. In ANSI-compliant SQL databases, double quotes are reserved for object/column names that contain non-standard characters. An example of an ANSI-compliant column name would be CUSTOMER_ID_NUM (quotes are optional); an example of a non-compliant column name would be "customer-id-#" (in which case, quotes are mandatory).

in your PROC SQL statement ...

change: &where

to: %SYSFUNC(TRANSLATE(&where, %BQUOTE("'), %BQUOTE('")))

Alternatively, you should avoid using double quotes altogether, for anything that follows the "connection to ODBC ... " statement.

%macro data (start_date, where, outdata);

  PROC SQL;
    CONNECT TO ODBC (USER=xxxxxxxxx PW=xxxxxxxxx DSN=xxxxxxx);
    CREATE TABLE DATA1 AS
    SELECT * FROM CONNECTION TO ODBC (
      SELECT DISTINCT
        (ESP01.CNTRCT_CONTRACT_R||ESP01.VEH_VIN_C) AS CONTRACT_VIN,
        EXTRACT(YEAR FROM ESP01.CNTRCT_START_Y) AS START_YEAR,
        CAST ( (ESP01.&START_DATE (FORMAT 'YYYYMM') ) as CHAR(6) ) as START_DATE, /*Contract start time for new vehicle*/
        ESP01.PLAN_CNT_CVG_TME_R as term
      FROM
        ESPARS_View.SSEPR01 as ESP01
      WHERE
        ESP01.CNTRCT_TEST_MKT_F = 'N' /* necessary constraint to get rid of the free contracts*/
        AND %SYSFUNC(TRANSLATE(&where, %BQUOTE("'), %BQUOTE('"))) /*Macro variable for all conditions to filter the contracts*/
        AND ESP01.PLBRN_PRDCT_LINE_C IN ('C', 'E', 'F')/* necessary constraint to pull the right contracts*/
      order by 1,2
    );
    DISCONNECT FROM ODBC;
  QUIT;

%mend;

Re: Character Value in Macro variable

Anuashla — Mon, 07 Dec 2015 15:01:37 GMT

I tried following the method you suggested using BQUOTE and Sysfunc but it still gives me the same error saying column PREM not found.

Re: Character Value in Macro variable

Patrick — Mon, 07 Dec 2015 19:37:37 GMT

@Anuashla

Please look at the code I've previously posted. It's a working sample which you can run in your own environment if you wish to. If things don't work for you then you must be doing something differently.

Please post your code - and/or post the log where you run your code with option MPRINT turned on; and then post the parts where we see to what the macro resolves to as well as the part where SAS throws an error.