topic Re: numeric works, non-numeric fails in my where statement in SAS Enterprise Guide

numeric works, non-numeric fails in my where statement

stogez13 — Wed, 28 Feb 2018 11:13:34 GMT

Hi, When I run this piece of code (query a numeric variable) it works proc sql noprint; select Centre, Branch, Product, Contract into :Centre, :Branch, :Product, :Contract from DD1; %put &Centre &Branch &Product &Contract; PROC SQL; connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD2 AS SELECT * FROM CONNECTION TO odbc ( SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, FIELD1 AS Key FROM EXAMPLETABLE where FIELD1=&Centre AND FIELD2=&Branch AND FIELD3=&Product AND FIELD4=&Contract for fetch only with ur; ); This returns the data I was expecting - but when I run a similar piece of code it fails and I think it's because the variable I'm querying in the WHERE statement is non-numeric. Do I need to do something different? proc sql noprint; select CheckUp1, CheckUp2 into :CheckUp1, :CheckUp2 from DD2; %put &CheckUp1 &CheckUp2; PROC SQL; connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD3 AS SELECT * FROM CONNECTION TO odbc ( SELECT FIELD6, FIELD7, FIELD8, FIELD9, FIELD6 AS Key FROM EXAMPLETABLE2 where FIELD6=&CheckUp1 AND FIELD7=&CheckUp2 for fetch only with ur; ); Any help much appreciated - thanks

Re: numeric works, non-numeric fails in my where statement

RW9 — Wed, 28 Feb 2018 11:27:11 GMT

[Big Wall of Text] - Sorry, can't make head not tail of it. Please reformat your post, use returns, paragraphs and such like so text is readable. For code, use the code blocks - it is the {i} above the post area - this will highlight code and retain source formatting so that it is readable.

I suspect on your second query, you would need to put the macro variables in quotes otherwise they appear as numbers:

where FIELD6="&CheckUp1." AND FIELD7="&CheckUp2."

Re: numeric works, non-numeric fails in my where statement

stogez13 — Wed, 28 Feb 2018 11:38:59 GMT

Sorry - first post 😞

Will try to reformat code to make it understandable - thanks

Re: numeric works, non-numeric fails in my where statement

stogez13 — Wed, 28 Feb 2018 11:42:59 GMT

proc sql noprint; 
select Centre, Branch, Product, Contract 
into :Centre, :Branch, :Product, :Contract 
from DD1; 
%put &Centre &Branch &Product &Contract; 

PROC SQL; 
connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD2 AS SELECT * FROM CONNECTION TO odbc 
( 
SELECT FIELD1, FIELD2, FIELD3, FIELD4, FIELD5, FIELD6, 
FIELD1 AS Key 
FROM EXAMPLETABLE 
where FIELD1=&Centre AND 
FIELD2=&Branch AND 
FIELD3=&Product AND
 FIELD4=&Contract 
for fetch only with ur; 
); 

This returns the data I was expecting - but when I run a similar piece of code it fails and I think it's because the variable I'm querying in the WHERE statement is non-numeric. Do I need to do something different? 

proc sql noprint; 
select CheckUp1, CheckUp2 
into :CheckUp1, :CheckUp2 from DD2; 
%put &CheckUp1 &CheckUp2; 
PROC SQL; 
connect to odbc (DSN=GBGB user=&DB2uid password=&DB2pwd); CREATE TABLE DD3 AS SELECT * FROM CONNECTION TO odbc 
( 
SELECT FIELD6, FIELD7, FIELD8, FIELD9, 
FIELD6 AS Key 
FROM EXAMPLETABLE2 
where 
FIELD6=&CheckUp1 AND 
FIELD7=&CheckUp2 
for fetch only with ur; 
);

Re: numeric works, non-numeric fails in my where statement

Kurt_Bremser — Wed, 28 Feb 2018 11:45:24 GMT

With character variables, use quotes as suggested by @RW9

Re: numeric works, non-numeric fails in my where statement

stogez13 — Wed, 28 Feb 2018 11:47:51 GMT

The second bit returns the following error

ERROR: CLI describe error: [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]EH3920197742 NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE.

If I put FIELD6="&CheckUp1" AND FIELD7="&CheckUp2" it doesn't return anything

But if I put FIELD6='EH3920197742' then it works - so I know the data is there, it just can't find it when I use the variable name

Re: numeric works, non-numeric fails in my where statement

RW9 — Wed, 28 Feb 2018 12:53:13 GMT

And what does &Checkup1 and 2 look like? Look in your log, turn macro printing functions on with:

options mlogic mprint symbolgen;

Does &Checkup1 = EH3920197742? I would guess it doesn't.

Re: numeric works, non-numeric fails in my where statement

Kurt_Bremser — Wed, 28 Feb 2018 13:04:19 GMT

@stogez13 wrote:

The second bit returns the following error

ERROR: CLI describe error: [DataDirect][ODBC DB2 Wire Protocol driver][UDB DB2 for OS/390 and z/OS]EH3920197742 NOT COLUMN OF INSERTED/UPDATED TABLE, OR ANY TABLE IN A FROM CLAUSE.

If I put FIELD6="&CheckUp1" AND FIELD7="&CheckUp2" it doesn't return anything

But if I put FIELD6='EH3920197742' then it works - so I know the data is there, it just can't find it when I use the variable name

This might be because ANSI SQL doesn't like double quotes for string literals. Try

FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))

Re: numeric works, non-numeric fails in my where statement

Tom — Wed, 28 Feb 2018 13:19:52 GMT

This might be because ANSI SQL doesn't like double quotes for string literals. Try
FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))

You don't want or need macro quoting here. The values are already in regular quotes!.

So just use normal %SYSFUNC() instead of %QSYSFUNC().

Re: numeric works, non-numeric fails in my where statement

Kurt_Bremser — Wed, 28 Feb 2018 13:24:03 GMT

Have to say, I just copied it from a solution that google found for me 😉

Re: numeric works, non-numeric fails in my where statement

stogez13 — Wed, 28 Feb 2018 14:11:00 GMT

Thank you so much - this worked like a dream!

🙂