Nick Betcher Nick Betcher - 4 months ago 20
SQL Question

SQL Accessing SAS Variable in Query

I'm attempting to use SASDOS in my statement below, but it's failing to be found. My understanding is that I have to use a form of derived table to access this new column. Is this correct? If so, could someone please help elaborate on how to do that?

proc sql;
create table TEST as
select
DQBBDA AS 'Sbm Date'n,
case when 'Sbm Date'n > 999999
then input('1' || substr(put('Sbm Date'n,z8.),3), z7.)
end as SASDOS format=z7.
from
DB2SCHEMA.ORIGIN
where
SASDOS = 1130314;
quit;

Answer

As sasfrog commented, you need to add the CALCULATED keyword to refer to a new column in SAS SQL and you should refer to the native DB2 column in your query. For example:

proc sql;
    create table TEST as 
    select DQBBDA AS 'Sbm Date'n
         , case when DQBBDA > 999999
                then input('1' || substr(put(DQBBDA,z8.),3), z7.)
            end as SASDOS format=z7.
    from DB2SCHEMA.ORIGIN
    WHERE CALCULATED SASDOS = 1130314;
quit;

However, you really should rethink what you are doing and figure out how to write a WHERE clause that uses only columns from DB2; otherwise the entire table must be pulled back to SAS (a likely poor solution). Cases like this are probably better solved using a pass-thru query (where you can execute native SQL directly in DB2).

UPDATE: Here is another (tested) example using a SAS data set rather than a table from a LIBNAME reference. Notice I'm also correcting a syntax error with the input function (the last parameter should be 7. not z7.).

data ORIGIN;
  DQBBDA = 11130314; output;
  DQBBDA = 22130314; output;
run;
options validvarname=any;
proc sql;
    create table TEST as 
    select DQBBDA AS 'Sbm Date'n
         , case when DQBBDA > 999999
                then input('1' || substr(put(DQBBDA,z8.),3), 7.)
            end as SASDOS format=z7.
    from ORIGIN
    WHERE CALCULATED SASDOS = 1130314;
quit;
Comments