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
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
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 (/).
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
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
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.
sorry I wanted to put your answer as helpful (instead of mine, I didn't make any answer), could I somehow do that?
Hi - I just unmarked your answer per your request and marked the one directly above as helpful - is that correct?
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.