HenryFeldman Tracker

Re: write to excel without need for named range

HenryFeldman — Fri, 23 Feb 2018 16:11:55 GMT

Correct, the code currently doesn't support inserting cells into an existing sheet.

Re: write to excel without need for named range

HenryFeldman — Fri, 23 Feb 2018 14:49:53 GMT

The XLSX export code builds a range with the same name as the sheet.

You want to specify the name?

Henry

Re: Importing a date

HenryFeldman — Wed, 11 Feb 2015 17:15:47 GMT

The 39813 is the underlying value in excel spreadsheet cell. (Try entering a date value and then changing the format to number.) It is the number of days since 1/1/1900.

Re: Importing a date

HenryFeldman — Wed, 11 Feb 2015 17:09:40 GMT

Yup 1960. But the correction factor is right. about 60 * 365

Re: Importing a date

HenryFeldman — Tue, 10 Feb 2015 15:51:06 GMT

the proc import dbms=xls code looks at the indvidual cell contents. If a column has both numeric and character cells then it will be a character field.

Excel is # of days since 1/1/1900. SAS is # of days since 1/1/1970. So if you convert the character string to a numeric and apply a date format, the number will be 70 years in the future. The following equation needs to be applied to each date variable.

numdate = input(chardate,best.) - 21915;

(BTW, if your dates still 4 years in the past, then the spreadsheet has a 1904 base date and you'll need to ADD back in 1462.)

Here is my entire test program:

data;

chardate = "39000";

numdate = input(chardate,best.) - 21915;

format numdate date.;

put numdate;

run;

Hope that is helpful

Henry

(developer of the sas/acccess for pc files dbms=xls code)

Re: Importing a date

HenryFeldman — Tue, 10 Feb 2015 15:12:44 GMT

How are you reading in the excel spreadsheet? The code that reads spreadsheets scans the column to determine the field type. The implication is that it is seeing mixed numeric (formatted as dates) and character data. The 39000 number is the number of days since 1/1/1900. If you don't have many fields , I can give you an equation to get that Ito a SAS date. Henry