SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Reply
Super Contributor
Posts: 413

How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Hi,

I have Excel file dates in the form dd/mm/yyyy .

When I infile it into SAS it orders the dates into mm/dd/yyyy, so how can I reorder it back?

For example, in Excel I have 31/12/2010, when I infile it to SAS it makes it 12/31/2010, so how to make it back to 31/12/2010, i.e, how to change the day's place with the month's

Thank you

Super User
Posts: 17,785

Re: How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Apply the appropriate format.

data want;

set have;

format date ddmmyyd10.;

run;

Here' s a list of formats by category, look for the one you want, ddmmyy, then there are two options:

ddmmyy and ddmmyyx - you want the x one because you want to specify a delimiter of slash (/).

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Super Contributor
Posts: 413

Re: How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Hi Reeza,

I actually tried your method and I guess that in this situation its the most efficient

But I was wondering is it possible to change the places of specific numbers and/or letters with others?

Thank you

SAS Super FREQ
Posts: 8,743

Re: How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Hi, the short answer is NO. Dates, even when you read them from Excel are internally converted to the number of days since Jan 1, 1960. For example:

-2 internally stored would format as 30Dec1959

-1 ... as 31Dec1959

0 ... as 01Jan1960

1 ... as 02Jan1960

2 ... as 03Jan1960

3 ... as 04Jan1960

etc, etc.

  SAS does not store dates as a text string but as the internal number of days (both positive and negative) from Jan 1, 1960. So, when you use a different FORMAT, SAS displays the number as a readable date.

  So, asking whether it's possible to switch certain numbers (such as month) for other numbers (such as days) doesn't make sense when you have imported your data into SAS formats. The way the number is internally stored is either a positive or negative number on a time line and doesn't really include "months" or "days" until you apply the format. Just for fun, try the WEEKDATE. or WORDDATE. formats.

cynthia

Super User
Posts: 10,487

Re: How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Example of what you want it look like. There are many things possible using a custom format and/or parsing the parts of a date with the appropriate SAS functions. I would recommend strongly looking in the online help for the Date formats as there are way too many for me to list manually.

Super Contributor
Posts: 413

Re: How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

sorry I wanted to put your answer as helpful (instead of mine, I didn't make any answer), could I somehow do that?

SAS Employee
Posts: 232

Re: How to convert mm/dd/yyyy into dd/mm/yyyy in SAS

Hi - I just unmarked your answer per your request and marked the one directly above as helpful - is that correct?

Ask a Question
Discussion stats
  • 6 replies
  • 1571 views
  • 6 likes
  • 5 in conversation