topic Re: convert excel dates to numbers in SAS Programming

convert excel dates to numbers

jcurran1289 — Wed, 03 Aug 2016 22:24:15 GMT

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/283874#M57885)

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

Re: convert excel dates to numbers

ballardw — Wed, 03 Aug 2016 23:38:11 GMT

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.

Re: convert excel dates to numbers

RahulG — Thu, 04 Aug 2016 03:40:27 GMT

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)

Re: convert excel dates to numbers

Kurt_Bremser — Thu, 04 Aug 2016 05:48:09 GMT

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.