Need help converting text date in dd/mm/yyyy format to numeric yyyy-mm-dd format.

[RandoDando]

Here is what the date looks like currently (character, length of 10):

Date

8/7/2023

9/30/2024

etc....

I tried this below but getting the error "Invalid argument to function INPUT"  Very very baffled as to why this isn't working.  Input is character to numeric, no?

New_date = Input(Date, mmddyy10.);

/*even tried adding this*/
format New_date yymmdd.;

[PeterClemmensen · Accepted solution]

Works fine? 

data have;
length date $10;
date = '8/7/2023'; output;
date = '9/30/2024'; output;
run;

data want;
   set have;
   New_date = Input(Date, mmddyy10.);
   format New_date mmddyy10.;
run;

[RandoDando]

That works. I guess SAS defaults to searching for the year in the first characters, so you have to tell it where the year is THEN re-arrange it.

[Tom]

---

@RandoDando wrote:
That works. I guess SAS defaults to searching for the year in the first characters, so you have to tell it where the year is THEN re-arrange it.

---

That does not make any sense.  The DDMMYY informat expects the parts to be in DMY order (as its name implies).

Are you sure the original variable is not already a numeric variable?

Are you sure there are not leading spaces or other invisible characters that would cause the string to be misunderstood by the DDMMYY informat?

[RandoDando]

No, the original variable was text. Just using INPUT(date, yymmdd10.) yielded blank values. Since the solution worked, I presumed this was because SAS did not know where to look for the day, month, and year values therefore requiring the user to tell it by first matching the current order of mmddyy.

[Tom]

Why would you try to use an informat that expects the data in YMD order when you knew the text had the values in DMY order?

[ballardw]

---

@RandoDando wrote:
That works. I guess SAS defaults to searching for the year in the first characters, so you have to tell it where the year is THEN re-arrange it.

---

This is one of the reasons that we ask for LOG entries from problems.

The only time SAS goes to anything resembling this level of guessing when reading dates is if an ANYDTDTE informat is used, and should only be used when the source has multiple types of date entries, at which point SAS "starts" by looking for delimited 4 digit strings for years and if it doesn't find any will start applying LOCALE options national language settings to 2-digit values. The other informats have specific rules and the name typically spells them out with M=Month, Y=Year and D= day of month in some order. So YYMMDD is used to read Year, Month, Day  order values, DDMMYY for Day Month Year, MMDDYY for Month, Day, Year, YYMMN reads Year Month (assumes the day of the month is 1), YYQ reads Year and calendar Quarter, MONYY read three-letter month abbreviation and year (again assumes day of month is 1). And others. But they all represent a specific set of rules related to the order the values appear.

Which why if you give us an example of 01/02/03 you MUST tell us which is month, which is day and which is year as there is nothing in that value that provides enough information to tell.

Let us know when you get a date value that looks like: 2582024 or look up Julian Dates.

Special informats and formats will read/write calendar quarters

[ballardw]

---

@RandoDando wrote:

Here is what the date looks like currently (character, length of 10):

Date

8/7/2023

9/30/2024

etc....

I tried this below but getting the error "Invalid argument to function INPUT"  Very very baffled as to why this isn't working.  Input is character to numeric, no?

New_date = Input(Date, mmddyy10.);

/*even tried adding this*/
format New_date yymmdd.;

---

Any time you are going to ask about an error message such as the "Invalid argument" you should include the LOG that shows the entire data step or procedure plus all the notes and messages generated by that message. Copy all the text in the log, on the forum open text box using the </> icon that appears above the message window and paste the text.

For example, it would tell us if every single observation generated that message or just some. With dates, depending on your source, there are typical data entry errors. Example:

847  data example;
848     x='11/31/2024';
849     y=input(x, mmddyy10.);
850  run;

NOTE: Invalid argument to function INPUT at line 849 column 6.
x=11/31/2024 y=. _ERROR_=1 _N_=1
NOTE: Mathematical operations could not be performed at the following places. The results of the
      operations have been set to missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 849:6
NOTE: The data set USER.EXAMPLE has 1 observations and 2 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Which is because month 11, i.e. November, only has 30 days. It is not uncommon for human entered values to have months longer than they should be, or for February some leap days in the wrong year.

If the pattern is that the invalid data is always at the end of a month then code can actually be written that would impute the end or first of the month for incorrect dates if desired.