DATA Step, Macro, Functions and more

convert excel dates to numbers

New Contributor
Posts: 4

convert excel dates to numbers


I am having a problem with a variable that is has two different formats in excel, number and date. 


I have tried the following piece of code which helped me before with a problem i had



if index(num(k),'/') >0 then num_form(k)=input(num(k), mmddyy10.);
else if num(k)~='missing' then num_form(k)= input(num(k), 8.);


i was planning to convert to a date then back to a number but it didnt work which you can see in the image i attached. i have also tried 


if index(num(k),'/') >0 then num_form(k)=input(num(k), 8..);
else if num(k)~='missing' then num_form(k)= input(num(k), 8.);


which leaves me with blanks where the dates were.


can someone please help??

Super User
Posts: 13,507

Re: convert excel dates to numbers

Posted in reply to jcurran1289

Is the variable that you have in SAS numeric or character when you read the data? If numeric then all the character stuff is likely gone.


I suspect that easiest would be to go back to Excel. Make sure the date column(s) are formatted as dates.

Do a file>save as> to a CSV file. Then use proc import or the import wizard to read the data. The the GUESSINROWS value to either 32000 or the number of rows in the file, which ever is smaller.


You have a much better chance of getting the values you want and as dates.

Super Contributor
Posts: 271

Re: convert excel dates to numbers

Excel consider 1Jan1900 as 0

SAS  consider 1Jan1960 as 0


So while converting date to number make use of following formula

SAS_date_time = (Excel_date_time - 21916)

Super User
Posts: 10,211

Re: convert excel dates to numbers

Actually, the zero day in SAS is 01jan1960, while the zero day in Excel is really 30dec1899.

Excel displays the value 1 as 01jan1900, but this is wrong, as Excel (current version 2010) still considers 1900 to be a leap year (with a February 29 that never existed), a bug that is fixed in other office suites. This is also the reason why Excel can't work with negative values as dates, in contrast to LibreOffice et al.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation