Solved

SAS DATE Conversion

Posted on 2009-04-14
8
1,718 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
  • 4
  • 3
8 Comments
 
LVL 9

Accepted Solution

by:
bradanelson earned 500 total points
Comment Utility
Use the DATEPART() function.

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

Author Comment

by:matheinjoe
Comment Utility
  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
Comment Utility
Ok, I figured out my error.  What an idiot I am.  LOL  Let me try it now.
0
 
LVL 9

Expert Comment

by:bradanelson
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

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

Expert Comment

by:wigmeister
Comment Utility
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
Comment Utility
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
Comment Utility
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Join & Write a Comment

In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now