SQL在查询中访问SAS变量(SQL Accessing SAS Variable in Query)

SQL在查询中访问SAS变量(SQL Accessing SAS Variable in Query)

我试图在下面的声明中使用SASDOS，但它未能找到。 我的理解是我必须使用派生表的形式来访问这个新列。 它是否正确？ 如果是这样，有人可以帮助详细说明如何做到这一点？

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;

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;

最满意答案

正如sasfrog评论的那样，您需要添加CALCULATED关键字以引用SAS SQL中的新列，您应该在查询中引用本机DB2列。 例如：

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;

但是，你真的应该重新考虑你在做什么，并弄清楚如何编写一个只使用DB2列的WHERE子句; 否则必须将整个表拉回SAS（这可能是一个糟糕的解决方案）。 使用pass-thru查询（您可以直接在DB2中执行本机SQL）可以更好地解决这种情况。

更新：这是另一个（测试的）示例，它使用SAS数据集而不是LIBNAME引用中的表。 注意我也用input函数纠正语法错误（最后一个参数应该是7.而不是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;

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;