How do I configure regional date formats?

Daniel Batey
Daniel Batey New Altair Community Member
edited January 3 in Community Q&A

We are in the process of moving to a new server and have installed the latest 2023 version of Analytics Workbench.  We are testing some SAS scripts that worked on our old server and have encountered an issue with dates.  When I run a program that uses proc import to read in an Excel file, dates are defaulting to a string in mm/dd/yy format but when my colleague runs exactly the same program it defaults to dd/mm/yy as expected.  On our old server both of us run the same code and it correctly loads as dd/mm/yy.  

We have checked our server and it is set to GB date format.  And when I check my user details on office.com it is showing as GB default date format.  

Where is this setting being picked up from and how can I change it?  I don't really want this to be user specific either for obvious reasons - the same code resulting in two different results is not good at all!

Thanks

Daniel

Tagged:

Best Answer

  • Philip Holland
    Philip Holland Altair Community Member
    edited January 3 Answer ✓

    Hi Daniel,

    The SAS language includes a number of options to control the layout of dates, but I'm not sure whether your problem relates to MS Office, Altair SLC, or the way you are importing the Excel data.

    Anyway, the SAS national options are:

    • LOCALE=en_GB (also en_US, fr_FR, fr_BE, fr_CH, de_DE, de_CH, hu_HU), which controls language, dates, currency, etc.
    • DATESTYLE=DMY (also MDY, YMD, LOCALE), which controls only reading and writing dates.
    There are also dozens of date formats, some of which are locale-specific:
    • You appear to be using a default of DDMMYY8.
    • Others include MMDDYYw. YYMMDDw., DATEw., NLDATEw., MONYYw., etc., where w=width.
    .............Phil
     

Answers

  • Philip Holland
    Philip Holland Altair Community Member
    edited January 3 Answer ✓

    Hi Daniel,

    The SAS language includes a number of options to control the layout of dates, but I'm not sure whether your problem relates to MS Office, Altair SLC, or the way you are importing the Excel data.

    Anyway, the SAS national options are:

    • LOCALE=en_GB (also en_US, fr_FR, fr_BE, fr_CH, de_DE, de_CH, hu_HU), which controls language, dates, currency, etc.
    • DATESTYLE=DMY (also MDY, YMD, LOCALE), which controls only reading and writing dates.
    There are also dozens of date formats, some of which are locale-specific:
    • You appear to be using a default of DDMMYY8.
    • Others include MMDDYYw. YYMMDDw., DATEw., NLDATEw., MONYYw., etc., where w=width.
    .............Phil
     
  • Nico Chart_21517
    Nico Chart_21517
    Altair Employee
    edited December 2023

    Hi Daniel,

    You may also need to consider the distinction between the storage of a date as a number vs. as a string. SAS language uses numbers for dates but then displays them using formats. Excel does the same. Are you writing a number to the Excel file, and allowing Excel to choose how to display it (that's probably the best approach if you want your file to be opened on different computers by different users who may have different date display preferences). As a test, try exporting to a CSV file, which contains only values not formats, and load that into Excel - this may help you get a clearer picture of what is being stored into your Excel files.

    Hope that's useful?

    Best Regards,
    Nico

  • Daniel Batey
    Daniel Batey New Altair Community Member
    edited December 2023

    Thanks both.  I added the locale En_GB to our cfg file and that seems to have worked.  At least it should now be consistent for all users.  I still don't quite understand where it was getting this setting from but at least it works.

    Daniel

  • Philip Holland
    Philip Holland Altair Community Member
    edited December 2023

    Daniel,

    Please could you mark your question answered, or, at least, mark those answers that were helpful?

    Thanks..........Phil

Categories