So I'm working from a source file that has a horizontal Table. (Random fictional data below)
Customer 00234 00236
Sales 23 45
Service Hours 01:15 02:30
Billable $28.75 $112.50
If I just imported as stored, SAS auto decides how to format, and since all the numbers different types data was lost. (Particularily since SAS and Excel stores time formats in different ways) Also, actual report is more involved than this simplified version, but that's off topic.
So my original proc import solution was to import with this script, which brought everything in as text, that I was then able to Proc Transpose with the first column as the heading labels, and then convert formats as needed.
PROC IMPORT DATAFILE= "Customer_Tracker.xls" OUT= WORK.TEMP_REPORT
DBMS=EXCEL REPLACE;
SHEET="SHEET1 $";
RANGE="A8:C11";
GETNAMES=No;
MIXED=YES;
SCANTEXT=NO;
USEDATE=NO;
SCANTIME=NO;
RUN;
However, after getting it to run locally, they wanted it to be automated on a UNIX environment. DBMS=EXCEL doesn't work in UNIX.
So the UNIX friendly proc import is
PROC IMPORT DATAFILE= "Customer_Tracker.xlsx" OUT= WORK.TEMP_REPORT
DBMS=XLSX REPLACE;
RANGE="SHEET1 $A8:C11";
GETNAMES=No;
RUN;
The Mixed, Scantext, Usedates, and Scantime our out of syntax for DBMS=XLSX. So I'm back to square one where SAS is auto formating the columns into values that are no longer useable for the end product.
So the question remains, how can I get SAS to import using DBMS=XLSX and force it to just read everything in as text so I can control how I want columns formatted after running the proc transpose?
... View more
