0

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;

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.

BluesGotTheCup
  • 43
  • 10
  • When using Pass through your query needs to be MYSQL compliant. I'm not familiar with MYSQL but that doesn't look correct - specifically the `format mmddyy10.` which looks like SAS code. Is this `||` also valid in MYSQL? – Reeza May 25 '21 at 20:56
  • Great call out, i will update this as it appears i am using SQL server. I have used FORTMAT MMDDYY10 Previously with no issues and am looking into if i can use || – BluesGotTheCup May 25 '21 at 21:03
  • Does query work by itself in SQL Server without `format` and `Sysdate`? I see a period qualifier name `SD`? What is this data source? – Parfait May 26 '21 at 00:19
  • I have ran a simple select (S.PROD_CD || S.plt_CD) AS SKU from SHPMT_DTL S and it worked as needed so i am assuming this is not the issue. I have also removed thed the Format part of the query along with the sysdate clause and it returned the same error so i am assuming those arent the culprit either. I also droped the period qualifiers as that was just referencing the datebase the table was stored in which is not needed since they all fall under the same master datebase im connected to. – BluesGotTheCup May 26 '21 at 01:01

1 Answers1

1

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

  1. Remove format
  2. Replace || with CONCAT() or COALESCE() as needed
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;

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.

Reeza
  • 20,510
  • 4
  • 21
  • 38