• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1766
  • Last Modified:

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?
0
matheinjoe
Asked:
matheinjoe
  • 4
  • 3
1 Solution
 
bradanelsonCommented:
Use the DATEPART() function.

PROC SQL;
    SELECT DATEPART([datetime_fieldname]) AS Date
    FROM [your dataset];
QUIT;
0
 
matheinjoeAuthor Commented:
  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

0
 
matheinjoeAuthor Commented:
Ok, I figured out my error.  What an idiot I am.  LOL  Let me try it now.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
bradanelsonCommented:
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;

0
 
matheinjoeAuthor Commented:
New Error:  The datepart function requires 2 argument(s)
0
 
wigmeisterCommented:
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.
0
 
matheinjoeAuthor Commented:
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

0
 
bradanelsonCommented:
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;


0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now