sas >> Date/time to date format in PROC SQL

by cjohns38 » Wed, 23 Dec 2009 01:35:09 GMT

I=92m 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=3Dmmddyy8. AS DOB2
FROM MDSVERI.Assessment1 AS A FULL JOIN ACCESS.Table1 AS B
ON A.ID =3D B.ID;
QUIT;

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

sas >> Date/time to date format in PROC SQL

by HERMANS1 » Wed, 23 Dec 2009 02:07:27 GMT


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: XXXX@XXXXX.COM ] On Behalf Of Craig Johnson
Sent: Tuesday, December 22, 2009 12:35 PM
To: XXXX@XXXXX.COM
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?

sas >> Date/time to date format in PROC SQL

by fdezdan » Wed, 23 Dec 2009 23:24:53 GMT

hi Craig ,

use these functions:

PUT(INPUT(scan(MYTIME,-4,':'),date9.),mmddyy10.)

data have;
input mytime $40.;
cards;
13JUL2006:00:00:00
19DEC2009:23:14:00
;
run;

PROC SQL;
CREATE TABLE TEST AS
SELECT put(INPUT(scan(MYTIME,-4,':'),date9.),mmddyy10.) as DOB2
FROM HAVE;
QUIT;


Daniel Fernandez
Barcelona

2009/12/22 Craig Johnson < XXXX@XXXXX.COM >:

sas >> Date/time to date format in PROC SQL

by cjohns38 » Wed, 23 Dec 2009 23:36:18 GMT

I received a handful of responses about this question. The most
parsimonious the following...

datepart(variablename) as newvarname, format=mmddyy10.

sas >> Date/time to date format in PROC SQL

by HERMANS1 » Thu, 24 Dec 2009 04:45:04 GMT

Drop the comma between the alias and the format= option. The SQL compiler may treat "format" as an attribute name.
S

-----Original Message-----
From: SAS(r) Discussion [mailto: XXXX@XXXXX.COM ] On Behalf Of Craig Johnson
Sent: Wednesday, December 23, 2009 10:36 AM
To: XXXX@XXXXX.COM
Subject: Re: Date/time to date format in PROC SQL

I received a handful of responses about this question. The most
parsimonious the following...

datepart(variablename) as newvarname, format=mmddyy10.

Similar Threads

1. creating a date time variable from separate date and time

2. creating a date time variable from separate date and time fields

Hello,

I have a file with a date variable and a time variable but can't find
way to create a datetime variable by combining the two variables. Any
suggestions would be appreciated.

Thanks,

Bill  West
 XXXX@XXXXX.COM 

3. Convert Character Date/Time to Text Date/Time

4. how to transfer this format date to date variable

Hi

I have a question regarding to transfer numerical variable to date variable.

My original variable is 991225 = 1999 Dec.25; 1011225 = 2001 Dec.25

How can I transfer this variable to a date variable, since I want to
calculate the days difference.

Thanks

Jane

5. SAS date format for reading Sybase date output

6. SAS DATE format - identifying blank dates

Hi,

I have a variable called "start_date" and I formatted it as follows:

   format start_date DATE9.

I found in my dataset that not all participants filled in this field.
I would like to identifying these missing records.  How do i do this?

I've tried reformatting this variable to a character and then putting
if start_date = ' ' then missing=1.  And I've even tried to put if
start_date = .  then missing=1.  But the format DATE 9. is neither
character nor numeric.

Can anyone help?

Thanks.

7. How to change char date to date format

8. SAS - Date Formats/Date manipulation

Hello everyone,



I had to work on a report where it involves Date operation / Date
manipulation

Like adding 3 months from a date , adding 1 week , 2 weeks =85.upto 12 week=
s
..etc



I need some best reference for SAS Date manipulation , formats , date
conversation ..etc, can someone please let me know
If anyone have any code with date manipulation , would be great for
reference.


Thanks

-          Swamy