BookmarkSubscribeRSS Feed
Anuashla
Calcite | Level 5

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.

 

13 REPLIES 13
Reeza
Super User

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. 

Anuashla
Calcite | Level 5

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;

ballardw
Super User

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.

Anuashla
Calcite | Level 5

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. 

Reeza
Super User
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?
Anuashla
Calcite | Level 5

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. 

Anuashla
Calcite | Level 5

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;

Patrick
Opal | Level 21

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

 

 

 

Anuashla
Calcite | Level 5

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

Patrick
Opal | Level 21

@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;

 

hbi
Quartz | Level 8 hbi
Quartz | Level 8

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;

 

 

Anuashla
Calcite | Level 5

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

Patrick
Opal | Level 21

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 2317 views
  • 0 likes
  • 5 in conversation