Best format to use when importing date in MS Access table into SAS

[jcis7]

I am a novice with dates SAS.    And  I still do not know how to convert character date fields into actual SAS dates.

We're setting up data entry in a MS Access datable and trying to decide what our date fields need to look like (i.e, for March 5, 2009, enter as 3/5/2009 or 03/05/2009;?  And what about field type:  Text or Date/Time?) so that when they're imported into SAS, we can easily convert them to a SAS date.

Since we want the people who are entering data to be able to copy and paste between date fields in the data entry form, having input mask on the data entry form for each of the date fields doesn't work (i.e,, prevents copy and pasting between fields).

The field in the table itself, however, is Date/Time for Type and has an input mask (short date) as well.

What is the best way for it to be setup so SAS can convert it to a SAS date in terms of:

In the MS Access Table:  Text?  or Date/Time

Have leading zeros (ie. 04/05/2009) or no leading zeros (4/5/2009)?

I tested this and found that with the Access table formatted as Date/Time with a short date input mask, regardless of whether leading zeros are entered into the data entry form, SAS imports them all as the following format:

04APR2009:00:00:00 which when I use proc contents, it's a DATETIME19. format.

Not sure how to convert that to a SAS date.

Any help you can give is much appreciated!

[Reeza · Accepted solution]

Datetime is a good format to use.

Use the datepart function in SAS to convert to a date field, but you can easily manipulate a datetime as needed.

Dates in SAS are stored as number of days from Jan 1, 1960 while datetimes are stored as number of seconds from Jan 1, 1960.

Then formats are used to display them in formats we non-computers can understand

[jcis7]

Got it- Thanks!