Solved

SAS DATE Conversion

Posted on 2009-04-14
8
1,733 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
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
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.

 
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 is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
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…

786 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