DATA Step, Macro, Functions and more

How to import Excel-data without ACCESS to PC Files

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

How to import Excel-data without ACCESS to PC Files

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.


Accepted Solutions
Solution
‎01-18-2017 04:46 AM
Super User
Posts: 7,824

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to Alkibiades

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-18-2017 04:46 AM
Super User
Posts: 7,824

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to Alkibiades

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 11,343

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to KurtBremser

Some other advantages with the CSV approach as suggested by @KurtBremser 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.

Super User
Posts: 19,822

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to Alkibiades

Try DBMS = XLSX

 

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

 

Occasional Contributor
Posts: 8

Re: How to import Excel-data without ACCESS to PC Files

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

 

SAS Super FREQ
Posts: 304

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to Alkibiades

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:

 

STPInstream.png

 

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:

 

AMOSelection.png

 

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:

 

AMOOutput.png

 

Vince DelGobbo

SAS R&D

Super User
Super User
Posts: 7,973

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to Alkibiades

@KurtBremser 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.  

Occasional Contributor
Posts: 8

Re: How to import Excel-data without ACCESS to PC Files

@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.

Super User
Posts: 7,824

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to Alkibiades

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Valued Guide
Posts: 505

Re: How to import Excel-data without ACCESS to PC Files

Posted in reply to KurtBremser
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;
');
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 999 views
  • 11 likes
  • 7 in conversation