How to import Excel-data without ACCESS to PC Files

[Alkibiades]

Is there a possibility to import xls/xlsx-files into SAS without having ACCESS to PC Files licensed? We have this need just sporadically so licensing ACCESS to PC Files is basically not worth the expenses.

Somewhere I read proc import with the option dbms=xls does not require ACCESS to PC Files. But I nonetheless get the error:

ERROR: DBMS type XLS not valid for import.

Is there any other possibility or do I have to convert the excel files to csv?

We use SAS 9.04 M3.

[Kurt_Bremser · Accepted solution]

Write the data to a text-based format from Excel (csv) and import from that. Reading text files is a part of Base SAS and needs no additional licenses at all.

This also has the great advantage that you can use a simple text editor to check what is written by Excel.

[ballardw]

Some other advantages with the CSV approach as suggested by @Kurt_Bremser are consistency between datasets. You set the properties for variables such as type, length and formats. Proc Import is a guessing engine and can result in variables changing from numeric to text or vice versa when importing different content with the same layout. Also the lengths of character variables are almost gauranteed to differ. Which can complicate analysis across two or more of these files imported.

The data step approach also allows many types of data checking at read time to see if your data entry is following rules.

One of my favorite things for fields that should have only certain values is to use custom informats to read the data and create error messages if the values appear out of the listed ones.

[Reeza]

Try DBMS = XLSX

Otherwise, you can try using an ODBC instead assuming you have that licence. 

[Alkibiades]

Sadly for the xlsx engine, ACCESS to PC Files is required. And no ODBC either.

Here's a list of our licensed products:

Product expiration dates:

---Base SAS Software 30OCT2017

---SAS/GRAPH 30OCT2017

---SAS/CONNECT 30OCT2017

---SAS OLAP Server 30OCT2017

---MDDB Server common products 30OCT2017

---SAS Integration Technologies 30OCT2017

---SAS/Secure 168-bit 30OCT2017

---Unused OLAP Slot 30OCT2017

---SAS/ACCESS Interface to Oracle 30OCT2017

---SAS Grid Manager 30OCT2017

---SAS Metadata Bridge for Microsoft Excel 30OCT2017

---SAS Metadata Bridge for Oracle 30OCT2017

---SAS Metadata Bridges for General Industry Standards 30OCT2017

---SAS Workspace Server for Local Access 30OCT2017

---SAS Workspace Server for Enterprise Access 30OCT2017

---SAS Add-in for Microsoft Excel 30OCT2017

---SAS Add-in for Microsoft Outlook 30OCT2017

---SAS Add-in for Microsoft PowerPoint 30OCT2017

---SAS Add-in for Microsoft Word 30OCT2017

---SAS Visual Analytics Services 30OCT2017

[Vince_SAS]

The PROC SETINIT output indicates that SAS/IML isn't licensed so the SAS interface to R is not an option.

You do license the SAS Add-in for Microsoft Excel and SAS Integration Technologies so I think you can use a stored process to upload the data directly from Excel to the SAS server.

Create a stored process with this code:

%STPBEGIN; 
 
libname inxml xml; 
 
data work.mydata; 
set inxml.&_WEBIN_SASNAME; 
run; 

*  Optional - display output in Excel;

title 'Data Received from Excel'; 
proc print data=work.mydata; run; quit; 
 
%STPEND;

During the registration process specify Stream and Package for Result capabilities, and then these options to create a new input data source:

When you run the stored process using the SAS Add-in for Microsoft Office you are asked to select the data.  For example, select A1:C2:

The data is pushed to the SAS server, a temporary data set named WORK.MYDATA is created, and then the PROC PRINT output is displayed in Excel:

Vince DelGobbo

SAS R&D

[RW9]

@Kurt_Bremser has the correct answer here.  Reliance on proprietary file formats is never a good idea.  Excel is neither a good data medium, nor is it open (no matter what they call it - Open Office).  Use CSV - plain text Comma delimited or XML will mean you can access your file on any computer, using more or less any software, and can import it spefically (i.e. you write a datastep to tell SAS exactly how to import that data - so avoiding the wolliness of proc importing spreadsheets) to any software.  Really, the only two benefits of Excel are the GUI which makes data entry easy (and of course colored cells and graphs to make management happy), and VBA which is a pretty powerfull inbuilt language.  

[Alkibiades]

@RW9 thanks for the reply, I suggested as much. The only point we use excel sheets is in fact indeed data entry. We let the departments maintain excel sheets to control scheduled programs which is easier for them. We will look into converting the xlsx files automatically on the server to csv so the users don't have to adjust.

Thanks to you all very much for your help.

[Kurt_Bremser]

I would move that data entry away from Excel into a small webapp with SAS stored processes. That enables sensible input validation and central control.

[rogerjdeangelis]

SAS Forum: How to import Excel-data without ACCESS to PC Files

If you have the IML interface to R you can easily recode my solution.

Python and Perl can also read sas7bdat and write to excel.

inspired by
https://goo.gl/HuxBtO
https://communities.sas.com/t5/Base-SAS-Programming/How-to-import-Excel-data-without-ACCESS-to-PC-Files/m-p/325568


HAVE SAS DATASET
================

Up to 40 obs from sd1.class total obs=6

Obs    NAME                 AGE

 1     Alfred                14
 2     Alice                 13
 3     Barbara               13
 4     Carol                 14
 5     Henry                 14
 6     James                 12


WANT (EXCEL SHEET)

Sheet HAVE

 +-------------------------+
 |      |    A      |   B  |
 +------+------------------+
 |      |           |      |
 |    1 |   NAME    |   AGE|
 |    2 |   Alfred  |   14 |
 |    3 |   Alice   |   13 |
 |    4 |   Barbara |   13 |
 |    5 |   Carol   |   14 |
 |    6 |   Henry   |   14 |
 +------------------+------+

SOLUTION WORKING CODE ( CAN RECODE IN IML INTERFACE TO R)

  R  writeWorksheet(wb,have,sheet="have");

FULL SOLUTION
==============

* CREATE SOME DATA;
options validvarname=upcase;
libname sd1 "d:/sd1";
data sd1.class;
  set sashelp.class(keep=name age obs=6);
run;quit;

%utl_submit_r64('
library(haven);
library(XLConnect);
have<-read_sas("d:/sd1/class.sas7bdat");
wb <- loadWorkbook("d:/xls/classout.xlsx",create = TRUE);
createSheet(wb, name="have");
writeWorksheet(wb,have,sheet="have");
saveWorkbook(wb);
run;quit;
');