Import excel file located on a UNIX server to SAS

1
votes

I am trying to import a excel file to a dataset using SAS, however the regular approach:

PROC IMPORT OUT= test.test
            DATAFILE= "M:/excelfile.xls"
            DBMS=EXCEL REPLACE;
     SHEET="Sheet1";
     GETNAMES=YES;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

only works on Windows.

Im using SAS 9.2 .. I tried different DBMS like XLS, EXCELCS with no luck... I've been investing a lot of time on this I wanted to know if is possible.. I don't have access to any other languages other than SAS ans SHELL (no PERL)

Is there anyway to accomplish what I'm looking for?

These are the installed base products

Operating System: AIX 64
---Base Product
---SAS/STAT

---SAS/GRAPH

---SAS/ETS
---SAS/OR ---SAS/CONNECT
---SAS Integration Technologies
---OR OPT
---OR PRS
---OR IVS
---OR LSO
---SAS/ACCESS Interface to DB2

4
exceldatasetsas

4 Answers

1
votes

If the list of installed base products on AIX is correct, you do not have the SAS Access to PC File Formats product licensed on UNIX. Since you mention "remote submit" and that you have SAS Access on your PC, you can transfer the file down to your PC, use PROC IMPORT, then transfer the SAS dataset back to the server:

rsubmit;
proc download infile='M:/excelfile.xls'
              outfile='c:\temp\excelfile.xls'
              binary;
run;
endrsubmit;

PROC IMPORT OUT=work.test
            DATAFILE= 'c:\temp\excelfile.xls'
            DBMS=EXCEL REPLACE;
    SHEET="Sheet1";
    GETNAMES=YES;
    MIXED=NO;
    SCANTEXT=YES;
    USEDATE=YES;
    SCANTIME=YES;
RUN;

rsubmit;
proc upload data=test out=test.test;
run;
endrsubmit;

Hope this helps, Bob

0
votes

See here - the options you can use seem to be dependent on the DBMS you specify (e.g. it looks like you cannot use GETNAMES with EXCELCS). Have you tried stripping the PROC IMPORT back, i.e. by dropping some options:

proc import out=work.test
  datafile = "/path/to/file.xls"
  dbms = EXCELCS replace;
  sheet = "Sheet1";
run;
0
votes

I know this is late but for the record you should be able to import an xlsx (XML Excel data sheet) with SAS using the XML data engine and a custom XMLMAP which is provided by SAS support.

See the paper here http://www2.sas.com/proceedings/sugi31/115-31.pdf for details of how to do this and where to get the XMLmap from.

0
votes

The problem may just be that you need to run SAS with the -noterminal option, as explained here.