asked on

SAS DATE Conversion

I have a field that is in the DATE TIME format, and I want to convert it in SAS SQL coding to a DATE only.

Any suggestions?

ASKER CERTIFIED SOLUTION

bradanelson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

matheinjoe

ASKER

79
ERROR 79-322: Expecting a ).

I keep getting the error above.

I have attached the code.

proc sql;
 
%odbc2;
 
create table work.Test1 as
 
SELECT * FROM connection to sqlsvr
 
(SELECT DATEPART([tsy.TsPostDate]) AS DATE, 
 
FROM System.dbo.tbl_Summary as tsy (nolock)
 
WHERE 
tsy.TsTrnStatus = 2
and
tsy.TsTrnTypeIdt IN (1,2,5)
and
tsy.TsPostDate Between '01/01/2009' And '1/31/2009'
 
 
quit;

Open in new window

matheinjoe

ASKER

Ok, I figured out my error. What an idiot I am. LOL Let me try it now.

bradanelson

Remove brackets around datetime field.

proc sql;

%odbc2;

create table work.Test1 as

SELECT * FROM connection to sqlsvr

(SELECT DATEPART(tsy.TsPostDate) AS DATE,

FROM System.dbo.tbl_Summary as tsy (nolock)

WHERE
tsy.TsTrnStatus = 2
and
tsy.TsTrnTypeIdt IN (1,2,5)
and
tsy.TsPostDate Between '01/01/2009' And '1/31/2009'

quit;

matheinjoe

ASKER

New Error: The datepart function requires 2 argument(s)

wigmeister

try
select datepart(tsy.TsPostDate) as date format=yymmdd10. --format to any date format you wish

Also, do you need the comma after DATE, before the From? That might be causing it to think a second argument is needed.

And if it were me I would change the AS DATE to another name because DATE is a keyword in so many languages. That's just me though.

I no longer have SAS to test anything or I would. Hope this helps.

What does your code look like now that you fixed the original issue? I assume you were missing an ending paren.

matheinjoe

ASKER

wigmeister,
Yes it was missing the closing paren. I have tried your suggestions above, and I still get the same error as above with the 2 arguments.

Any other suggestions.

See new code below

proc sql;
 
%odbc2;
 
create table work.Test1 as
 
SELECT * FROM connection to sqlsvr
 
(SELECT DATEPART(tsy.TsPostDate) as postdate format=yymmdd10.
 
FROM System.dbo.tbl_Summary as tsy (nolock)
 
WHERE 
tsy.TsTrnStatus = 2
and
tsy.TsTrnTypeIdt IN (1,2,5)
and
tsy.TsPostDate Between '01/01/2009' And '1/31/2009')
 
 
quit;

Open in new window

bradanelson

The DATEPART function only has 1 argument.

Syntax
DATEPART(datetime)

Arguments
datetime
specifies a SAS expression that represents a SAS datetime value.

I do notice that you are refrencing tsy.TsPostDate in the WHERE portion of your SQL. This will not work because SQL has not change this value to a date field. Try the following... If this doesn't work, take out the date criteria all together. It's easier to troubleshoot one thing at a time.

proc sql;

%odbc2;

create table work.Test1 as

SELECT * FROM connection to sqlsvr

(SELECT DATEPART(tsy.TsPostDate) AS DATE,

FROM System.dbo.tbl_Summary as tsy (nolock)

WHERE
tsy.TsTrnStatus = 2
and
tsy.TsTrnTypeIdt IN (1,2,5)

HAVING DATE Between '01/01/2009' And '1/31/2009')

quit;