turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- convert excel dates to numbers

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

08-03-2016 06:24 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to jcurran1289

08-03-2016 07:38 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ballardw

08-03-2016 11:40 PM

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)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to RahulG

08-04-2016 01:48 AM

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

Maxims of Maximally Efficient SAS Programmers