convert excel dates to numbers

[jcurran1289]

Hi, 

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

(Source: https://communities.sas.com/t5/Base-SAS-Programming/convert-variable-of-Char-and-numeric-to-date/m-p...)

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??

[ballardw]

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.

[RahulG]

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)

[Kurt_Bremser]

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.