Dynamic WHERE and DB2 pass thru

[Asender]

Hi All.

I have a problem with that attached code.(see bottom of post)

I’m creating a dynamic WHERE statement based on the values I find in the macgen dataset.

This works fine in a ‘normal’ SAS data step, however when I attempt the same in my SQL DB2 pass thru it spits out the following –

DSNT408I SQLCODE = -206, ERROR: N7 IS NOT A COLUMN OF AN INSERTED TABLE, UPDATED TABLE, OR ANY TABLE IDENTIFIED IN A FROM CLAUSE, OR IS NOT A COLUMN OF THE TRIGGERING TABLE OF A TRIGGER

The macro seems to resolve to the following –

MPRINT(GENIN): "N7" , "Q2" , "Q3" , "Q4" , "Q5" , "Q6" , "Q7"

I have gathered that DB2 SQL treats text values in double quotes differently. Not sure what is going on exactly.

What I would like to do is some how adjust the text generated by the macro to only have single quotation marks round the values or even persuade the DB2 system to treat them as normal text.

Any help would be appreciated.

Thanks for reading.

CODE LISTING

data _null_;
set macgen;
call symput('mvar'||left(_n_),trim(account_type));
call symput('max',left(_n_));
run;

%macro genin;
%do i =1 %to &max;
"&&mvar&i"
%if &i ne &max %then %do;
,
%end;
%put input = "&&mvar&i";
%end;
%mend genin;

proc sql;
create table WORK.&bank._V_MST_CUS_ACCONH as
select *
from connection to db2
(
select customer_id, branch_no, account_no, account_open_date
from &bank..V_MST_CUS_ACCONH
where relation_type in ('O','OJ') and
account_type in (%genin)
order by branch_no, account_no, account_open_date, customer_id
);
quit;

 

[klaz2002]

First I would add something to your datastep that would resolve quicker.
This is your code:

data _null_;        
  set macgen;       
  call symput('mvar'||left(_n_),trim(account_type)); 
  call symput('max',left(_n_));
run;

Here is what I would add:

data _null_;        
  set macgen END=LAST;       
  call symput('mvar'||TRIM(left(PUT(_n_,8.))),trim(account_type)); 
  IF LAST THEN
   call symput('max',TRIM(left(PUT(_n_,8.))) );
run;

This gets rid of those nasty warnings in the log.

OK Now on to your problem. While I wouldn't generate the text that way I understand that there are many ways to accomplish the same task. Using your macro I would add the following:

%macro genin;          
  %do i = 1 %to &max;
    %str(%')&&mvar&i%str(%')
    %if &i ne &max %then %do;
      ,
    %end;
    %put    input = &&mvar&i;
  %end;
%mend genin;

I am not sure of your DB2 IN statement and as I am not that current on that flavor of SQL I can't say that you have the right syntax. Perhaps you can test this in a native DB2 environment and see if it returns any records.

I hope that this helps you.
klaz

 

[Asender]

Hi klaz.

Thanks you for your reply.

Unfortunately I'm still not getting the results that I require.

I now appear to be getting unquoted values passed into the WHERE statement. However, as it is a character field I am searching the values need to be quoted.

I have incorporated your suggestion re the macgen datastep. Nice one.

Regards,

Asender.

 

[ChrisW75]

Check out the %BQUOTE

http://support.sas.com/onlinedoc/913/getDoc/en/mcrolref.hlp/z3514bquote.htm

and %QUOTE

http://support.sas.com/onlinedoc/913/getDoc/en/mcrolref.hlp/z3514quote.htm

Macro functions in the doco, they might be able to help you out. I think I've used these before to do something similar.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.