DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Regular Contributor
Posts: 194
Accepted Solution

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

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!


Accepted Solutions
Solution
‎06-23-2014 04:37 PM
Super User
Posts: 19,860

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

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 Smiley Wink

View solution in original post


All Replies
Solution
‎06-23-2014 04:37 PM
Super User
Posts: 19,860

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

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 Smiley Wink

Regular Contributor
Posts: 194

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

Got it- Thanks!Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 901 views
  • 0 likes
  • 2 in conversation