BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
stogez13
Calcite | Level 5
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
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

[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."

stogez13
Calcite | Level 5

Sorry - first post 😞

 

Will try to reformat code to make it understandable - thanks

stogez13
Calcite | Level 5
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; 
);
stogez13
Calcite | Level 5

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 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

@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'"))
Tom
Super User Tom
Super User

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().
 
stogez13
Calcite | Level 5

Thank you so much - this worked like a dream!

🙂

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 10 replies
  • 930 views
  • 0 likes
  • 4 in conversation