Date Formatting Issue

[sanjaymane7]

Hi, I have a SAS dataset wherein date format is incorrect and the same is to be corrected. 

Please help in the matter.

data A;
input Employee_Id $ Date Date9. ;
cards;

70202028 - 04JAN2023;

70204018 - 04SEP2023;

70172038  - 27MAR2023;

run;

data want;

set a;

70202028 - 01APR2023; /*to be changed*/

70204018 - 09APR2023; /*to be changed*/

70172038  - 27MAR2023; /*this is correct and it should not be changed*/

run; 

[yabwon]

Base on what rules those dates should be changed ?

[Tom · Accepted solution]

Where did this data come from? How did you get it into SAS.

It looks like you mistakenly read in values in MDY order as if they were in DMY order (or the reverse).

You should probably fix the step that makes the data originally.

If you really do want to switch the month and day when the day is less than 13 then you could do something like:

data want ;
  set have;
  if day(date) in (1:12) then date=mdy(day(date),month(date),year(date));
run;

PS Your posted data step cannot work.  Do not add the hyphens and semicolons into the in-line data lines.  Do not use FORMATTED MODE to read the DATE value, use LIST MODE instead.

data have ;
  input Employee_Id $ Date :date. ;
  format date date9.;
cards;
70202028 04JAN2023
70204018 04SEP2023
70172038 27MAR2023
;

[sanjaymane7]

Hi, I got this readymade file from someone. I was trying to correct it. Thank you so much. Today is my review meeting and you saved my job. Thank you again

[Tom]

---

@sanjaymane7 wrote:

Hi, I got this readymade file from someone. I was trying to correct it. Thank you so much. Today is my review meeting and you saved my job. Thank you again

---

So you have learned something.

1) Never use either MDY or DMY ordering when writing dates into text files (like CSV files).  Only use unambiguous styles like the DATE or YYMMDD formats produce.

2) Never use ANYDT... informats to read in date strings without first making sure that the DATESTYLE system option is set to the proper value for your data file.  Either MDY or DMY.