?
Solved

SAS DATE Conversion

Posted on 2009-04-14
8
Medium Priority
?
1,755 Views
Last Modified: 2013-11-16
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
Comment
Question by:matheinjoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 9

Accepted Solution

by:
bradanelson earned 1500 total points
ID: 24140418
Use the DATEPART() function.

PROC SQL;
    SELECT DATEPART([datetime_fieldname]) AS Date
    FROM [your dataset];
QUIT;
0
 

Author Comment

by:matheinjoe
ID: 24140609
  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
 

Author Comment

by:matheinjoe
ID: 24140634
Ok, I figured out my error.  What an idiot I am.  LOL  Let me try it now.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 9

Expert Comment

by:bradanelson
ID: 24140638
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
 

Author Comment

by:matheinjoe
ID: 24140646
New Error:  The datepart function requires 2 argument(s)
0
 
LVL 4

Expert Comment

by:wigmeister
ID: 24141457
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
 

Author Comment

by:matheinjoe
ID: 24141544
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
 
LVL 9

Expert Comment

by:bradanelson
ID: 24141686
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this article, we’ll look at how to deploy ProxySQL.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question