[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SAS DATE Conversion

Posted on 2009-04-14
8
Medium Priority
?
1,764 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

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 blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

656 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