proc sql pass through separate time in datetime stamp

paulsparrow — Tue, 18 May 2021 17:56:19 GMT

I am trying to separate the time from a datetime stamp field in a passthrough query. I am getting an error that it doesn't recongnize the command 'timepart'

PROC SQL ;
CONNECT TO odbc(dsn = 'xxxx' user = xxxxx pw = xxxx);
CREATE TABLE birds.pn01a AS
SELECT
*
FROM
connection to odbc
(
SELECT TOP 100
c.ELG_CUSTOMER_BUS_KEY AS Customer_Id,
p.PRODUCT_CATEGORY_NAME,
p.PRODUCT_BRAND_NAME,
p.PRODUCT_SUB_BRAND_NAME,
p.product,
f.DATE_KEY,
timepart(f.transaction_date) as time_,
f.transaction_date,
f.transaction_hour,
f.transaction_minute,
f."BET AMOUNT" AS Bet_Amt,
f."NET SALES AMOUNT" as Net_Sales,
f.transaction_cnt AS Games_Played

ERROR: CLI describe error: [Microsoft][ODBC SQL Server Driver][SQL Server]'timepart' is not a recognized built-in function name. :
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Re: proc sql pass through separate time in datetime stamp

r_behata — Tue, 18 May 2021 18:03:18 GMT

Try using SQL Server syntax inside Pass-thru query.

PROC SQL ;
CONNECT TO odbc(dsn = 'xxxx' user = xxxxx pw = xxxx);
CREATE TABLE birds.pn01a AS
SELECT
*
FROM
connection to odbc
(
SELECT TOP 100
c.ELG_CUSTOMER_BUS_KEY AS Customer_Id,
p.PRODUCT_CATEGORY_NAME,
p.PRODUCT_BRAND_NAME,
p.PRODUCT_SUB_BRAND_NAME,
p.product,
f.DATE_KEY,
CONVERT(TIME,f.transaction_date) as time_,
f.transaction_date,
f.transaction_hour,
f.transaction_minute,
f."BET AMOUNT" AS Bet_Amt,
f."NET SALES AMOUNT" as Net_Sales,
f.transaction_cnt AS Games_Played

topic proc sql pass through separate time in datetime stamp in SAS Programming

proc sql pass through separate time in datetime stamp

Re: proc sql pass through separate time in datetime stamp