Date:         Tue, 22 Dec 2009 13:07:27 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: Date/time to date format in PROC SQL
Comments: To: Craig Johnson <cjohns38@GMAIL.COM>
In-Reply-To:  <bfed04db0912220935u55a07f98l50bbf225b3b2b9@mail.gmail.com>
Content-Type: text/plain; charset="us-ascii"

The function DATEPART() does what its name implies; e.g., ...  DATEPART(B.DOB) as DOB format=mmddyy10. .... will yield a SAS date value formatted as specified and use the alias DOB to create a variable DOB in the yield of the query, TEST.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Craig Johnson
Sent: Tuesday, December 22, 2009 12:35 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Date/time to date format in PROC SQL

I'm pulling data from a database that is storing the date in a Date/Time
format.  I need to create a new dataset using a standard date format
(MM/DD/YYYY).  Is there a simple way to go from this 13JUL2006:00:00:00 to
this 07/13/2006 in PROC SQL?

Example Code I'm working with....

PROC SQL;
CREATE TABLE TEST AS
SELECT A.ID AS ID1, A.DOB AS DOB1, B.ID AS ID2, scan('B.DOB', -3, ':')
Format=mmddyy8. AS DOB2
FROM MDSVERI.Assessment1 AS A FULL JOIN ACCESS.Table1 AS B
ON A.ID = B.ID;
QUIT;

I'm sure there is a simple answer I'm just not sure what it is.....
thoughts?