This might be because ANSI SQL doesn't like double quotes for string literals. Try
FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))
[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."
Sorry - first post 😞
Will try to reformat code to make it understandable - thanks
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; );
With character variables, use quotes as suggested by @RW9
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
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.
@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'"))
This might be because ANSI SQL doesn't like double quotes for string literals. Try
FIELD6=%qsysfunc(dequote("'&CheckUp1'")) AND FIELD7=%qsysfunc(dequote("'&CheckUp2'"))
Have to say, I just copied it from a solution that google found for me 😉
Thank you so much - this worked like a dream!
🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.