rkellow
asked on
SAS Variable resolution on DB2 pass-thru PROC SQL
So I am running into a problem when I run the code below. I tested the pass-thru portion first with constants to validate if it worked. I then included it in my do loop with variables.
In the feedback log, the first 2 variables "&TABLENAME" and "&VARNAME&II." are resolving correctly, but the double quotes remain. This causes the pass thru to try to us the variables as valid fields to select, when I only want the value to be part of the result set.
I have tried single quotes, single quotes inside double quotes, and double quotes inside single quotes. None of these work. Any suggestions?
%do II=1 %to &NUMVARS.;
PROC SQL THREADS FEEDBACK;
CONNECT TO ODBC (DSN=XXXX USER=XXXXX PASSWORD=XXXXX);
create table work.query_output as
select "&TABLENAME." as table_name
,"&VARNAME&II." as field_name
,4 as test_num
,ROW_NUMBER() OVER () as seq_nbr
,&&VARNAME&II. as value
, count(1) as results
from PRDOTCDTA.&TABLENAME.
group by &&VARNAME&II.
order by results desc
fetch first 10 rows only);
quit;
%output_creation;
%if &II. ne &NUMVARS. %then %do;
%end;
%end;
In the feedback log, the first 2 variables "&TABLENAME" and "&VARNAME&II." are resolving correctly, but the double quotes remain. This causes the pass thru to try to us the variables as valid fields to select, when I only want the value to be part of the result set.
I have tried single quotes, single quotes inside double quotes, and double quotes inside single quotes. None of these work. Any suggestions?
%do II=1 %to &NUMVARS.;
PROC SQL THREADS FEEDBACK;
CONNECT TO ODBC (DSN=XXXX USER=XXXXX PASSWORD=XXXXX);
create table work.query_output as
select "&TABLENAME." as table_name
,"&VARNAME&II." as field_name
,4 as test_num
,ROW_NUMBER() OVER () as seq_nbr
,&&VARNAME&II. as value
, count(1) as results
from PRDOTCDTA.&TABLENAME.
group by &&VARNAME&II.
order by results desc
fetch first 10 rows only);
quit;
%output_creation;
%if &II. ne &NUMVARS. %then %do;
%end;
%end;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.