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.
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
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.