SAS: Convert Character Date Value to Numeric Value with format MMDDYY10

Question

i am trying to convert a column which contains dates. The column type is character and the format/informat is $5.

I want to have it in the format MMDDYY10.

example: one value is 44106 and it should be displayed as 02.10.2020

I tried it with

format var mmddyy10.

but it didnt work.

Could you please tell me how to deal with this problem? Thank you!

Did you create this SAS dataset from an Excel spreadsheet? Can you fix the spreadsheet to only have dates in the column you want to become a date variable in SAS and re-import it? — Tom
– Tom, Commented May 9, 2023 at 13:05
Yes, the data is from an excel. The column in the spreadsheet already has the right date format. But while importing the data it changes to the character format. — ng510
– ng510, Commented May 9, 2023 at 13:14
The column has at least one cell that has a string instead of date. That is why SAS made a character variable instead of a numeric varaible. — Tom
– Tom, Commented May 9, 2023 at 13:15

Tom · Accepted Answer · 2023-05-09 13:21:47Z

1

I doubt if that number represents the date 10FEB2020 that you suggested. More likely it represents 02OCT2020 instead.

That is because it looks like the string that SAS would generate for a date value that was included in column in an Excel spreadsheet that also had some cells with character strings.

To convert the string to a number use the INPUT() function. To convert the number that Excel uses for date to the corresponding number that SAS would use for the same date just add the value '30DEC1899'd. That will adjust for Excel using a different base date than SAS and also for difference in whether to count from zero or one and the fact that Excel considers 1900 a leap year.

If you did want to display the dates in the style MM.DD.YYYY then use the MMDDYYP10. format.

data want;
  set have;
  date = input(string,32.)+'30DEC1899'd ;
  format date mmddyyp10. ;
run;

edited May 9, 2023 at 13:21

answered May 9, 2023 at 13:14

Tom

52.3k2 gold badges19 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SAS: Convert Character Date Value to Numeric Value with format MMDDYY10

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related