我试图在下面的声明中使用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;更多推荐
发布评论