SAS PROC SQL Innerjoin 不返回任何列 [英] SAS PROC SQL Innerjoin returning no columns

问题描述

问题已解决Proc SQL 未返回任何列.感谢 TOM 的 Innerjoin.我感谢这里提供的帮助，但它并没有引导我找到正确的解决方案.我最初的编辑说我正在使用 mysql 但我在发布后对其进行了简短的编辑以确认它是 sql server.这导致这个头朝错误的方向发展.有关这方面的信息，请参阅上面链接的线程.

question was resolved Proc SQL not returning any columns. Innerjoin thanks to TOM. I appreciated the help offered here but it did not lead me to the correct solution. My original edit said i was using mysql but i edited this briefly after posting to confirm it was sql server. this lead to this thead going in the wrong direction. Please see thread linked above for info regarding this.

connect to odbc (dsn='X' uid='X' pwd='XY');
create table School.TRANS_INFO as SELECT * from connection to odbc
(SELECT Distinct
S.Schd_t AS Schd_Date format MMDDYY10.,
S.otb As Ship_Loc,
W.store_NUMBER AS store,
S.SHP_ID AS SHIP_ID,
W.store_CITY_STATE,
Q.Stat_CD AS Status,
(S.PROD_CD || S.plt_CD) AS SKU,
W.ACCOUNT_TYPE as Location
FROM
SHPMT_DTL S 
INNER JOIN store W ON W.store_NUMBER =  S.Otb_DEST_store_NBR
INNER JOIN SHP_LEG Q ON Q.SHPMT_ID = S.SHP_ID
WHERE
W.ACCOUNT_TYPE = 'I' 
AND S.Schd_t <= Sysdate 
AND Q.Stat_CD IN ('S','P')
ORDER BY  Schd_Date);
disconnect from odbc;
QUIT; 

但是，在我运行此程序后，我收到一条错误消息，提示PROC SQL 要求任何创建的表至少具有 1 列".任何诊断此问题的帮助将不胜感激.

However, after i run this i get an error saying "PROC SQL requires any created table to have at least 1 column". Any help to diagnose this issue would be appreciated.

推荐答案

试试这个，删除任何 SAS 特定的函数/操作.

Try this instead, removing any SAS specific functions/operations.

1. 删除格式
2. 替换 ||根据需要使用 CONCAT() 或 COALESCE()

proc sql;
connect to odbc (dsn='X' uid='X' pwd='XY');
create table School.TRANS_INFO as SELECT * from connection to odbc
(SELECT Distinct
S.Schd_t AS Schd_Date ,
S.otb As Ship_Loc,
W.store_NUMBER AS store,
S.SHP_ID AS SHIP_ID,
W.store_CITY_STATE,
Q.Stat_CD AS Status,
concat(S.PROD_CD, S.plt_CD) AS SKU,
coalesce(s.prod_cd, s.plt_cd as skU2,
W.ACCOUNT_TYPE as Location
FROM
SD.SHPMT_DTL S 
INNER JOIN PN_store W ON W.store_NUMBER =  S.Otb_DEST_store_NBR, 
INNER JOIN SD.SHP_LEG Q ON Q.SHPMT_ID = S.SHP_ID,
WHERE
W.ACCOUNT_TYPE = 'I' 
AND S.Schd_t <= Sysdate 
AND Q.Stat_CD IN ('S','P')
ORDER BY  Schd_Date);
disconnect from odbc;
QUIT; 

我认为 MYSQL 使用 || 作为 OR，它应该等同于 COALESCE()?在 SAS 中，这将是连接，因此不确定您要在这里做什么，因此我将其作为 SKU 和 SKU2 包含在我的答案中.

I think MYSQL is using || as OR which should be equivalent to COALESCE()? In SAS that would be concatenation so not sure what you're trying to do here so I included both in my answer as SKU and SKU2.

这篇关于SAS PROC SQL Innerjoin 不返回任何列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！