-2

I am trying to gather the information on which store is receiving which SKU along with some other relevant info. The data is in multiple tables so i am trying innerjoin the info. I am using SAS and it runs in SQL Server via passthrough. Below is the query i tried. I have confirmed that || works in the pass through. The issue seems to start in the from statement

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
  • 1
    Please at least fix the issues we already noted before reposting. https://stackoverflow.com/questions/67695383/sas-proc-sql-innerjoin-returning-no-columns – Reeza Jun 04 '21 at 17:39
  • Reeza, i am not sure what issues you are mentioning. I have used || and format mmddyy10 succesfully in similar queries. – BluesGotTheCup Jun 04 '21 at 17:59
  • 2
    This `S.Schd_t AS Schd_Date format MMDDYY10.` is SAS specific syntax. It will not work in the SQL-SERVER database. – Tom Jun 04 '21 at 19:08
  • @BluesGotTheCup not possible. Pass through queries require valid MS SQL code and FORMAT is not SQL code it's SAS code. – Reeza Jun 04 '21 at 19:19
  • 1
    Does this answer your question? [SAS PROC SQL Innerjoin returning no columns](https://stackoverflow.com/questions/67695383/sas-proc-sql-innerjoin-returning-no-columns) – Dale K Jun 04 '21 at 22:31
  • 1
    Please do not repost a duplicate question. – Dale K Jun 04 '21 at 22:32

1 Answers1

1

Move the SAS syntax to the SAS side of the query. You also had an extra comma in the middle of the FROM clause. Note if you get in the habit of putting the commas (or any other continuation characters) at the beginning of the line instead of the end they will be easier for the programmers to scan and be sure they are done correctly.

create table School.TRANS_INFO as 
SELECT Distinct
  Schd_t AS Schd_Date format MMDDYY10.
, otb As Ship_Loc
, store_NUMBER AS store
, SHP_ID AS SHIP_ID
, store_CITY_STATE
, Stat_CD AS Status
, (PROD_CD || plt_CD) AS SKU
, ACCOUNT_TYPE as Location
from connection to odbc
(SELECT Distinct
  S.Schd_t 
, S.otb 
, W.store_NUMBER 
, S.SHP_ID 
, W.store_CITY_STATE
, Q.Stat_CD
, S.PROD_CD
, S.plt_CD
, W.ACCOUNT_TYPE
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 S.Schd_t
);
Tom
  • 47,574
  • 2
  • 16
  • 29
  • First off, i want to say that Comma tip is incredibly useful as you are correct it is much easier to spot missing commas. Secondly, ive tried the above query with the proc sql and connect to odbc at the stop and a discconect from ODBC at the bottom but i got a "Columns were not found in the contributing tables: Account_Type, OTB, Prod_CD, plt_CD, shp_id, Stat_CD, Store_city_state, Store_number. " error – BluesGotTheCup Jun 04 '21 at 21:44
  • update: removed the disconnect from ODBC and it worked. Any idea why that wouldve been causing a problem. I did end up putting a quit; at the end as well – BluesGotTheCup Jun 04 '21 at 21:53
  • 1
    You really don't need the disconnect statement if you have the quit. Only real reason to have the disconnect is if you are going to do other long running queries in the same PROC SQL step and want to be nice and reduce load on the remote database by clearing the connection a little earlier. – Tom Jun 05 '21 at 14:04
  • Makes sense! Thanks! – BluesGotTheCup Jun 05 '21 at 21:52