topic Importing Excel files in syntax without SAS/ACCESS in SAS Procedures

Importing Excel files in syntax without SAS/ACCESS

matt_beyers — Wed, 05 Sep 2018 19:02:38 GMT

I'm trying to import my Excel files into SAS without SAS/ACCESS. Is this possible other than the SAS Enterprise Guide import wizard? I'm a newbie but I've checked around in here and on other sites with no luck.

I'm using base SAS 9.4 with SAS/STAT on a Windows 10 64-bit machine.

From the import wizard, I'm not seeing a way to use the syntax generated directly.

From https://libguides.library.kent.edu/SAS/ImportData#s-lg-box-wrapper-18345498:
This uses PCFILES, which seems to be part of SAS/ACCESS. I get the error, "ERROR: The PCFILES engine cannot be found."

From https://stats.idre.ucla.edu/sas/faq/how-do-i-readwrite-excel-files-in-sas/:
This uses PROC IMPORT with DBMS=XLSX (I've used DBMS=EXCEL, too). I get the error, "ERROR: DBMS type XLSX not valid for import."

Re: Importing Excel files in syntax without SAS/ACCESS

SuryaKiran — Wed, 05 Sep 2018 19:26:21 GMT

Convert your excel file into raw data file (csv, txt, ect..) and then import using the Data Step infile.

Re: Importing Excel files in syntax without SAS/ACCESS

Reeza — Wed, 05 Sep 2018 20:09:02 GMT

You can also set up an ODBC connection to a file and connect to it that way, after it's set up as a DSN connection.

This is a major annoyance if you have to do it for a lot of different files though, since it's a manual process.

You likely don't have access to your system to convert the XLSX files to CSV automatically, do you? If so, there's a VBS script floating around that will convert them and allow you to streamline this process a bit more. It's not possible to work both locally and on the server though so that's a different issue.

@matt_beyers wrote:

I'm trying to import my Excel files into SAS without SAS/ACCESS. Is this possible other than the SAS Enterprise Guide import wizard? I'm a newbie but I've checked around in here and on other sites with no luck.

I'm using base SAS 9.4 with SAS/STAT on a Windows 10 64-bit machine.

From the import wizard, I'm not seeing a way to use the syntax generated directly.

From https://libguides.library.kent.edu/SAS/ImportData#s-lg-box-wrapper-18345498:
This uses PCFILES, which seems to be part of SAS/ACCESS. I get the error, "ERROR: The PCFILES engine cannot be found."

From https://stats.idre.ucla.edu/sas/faq/how-do-i-readwrite-excel-files-in-sas/:
This uses PROC IMPORT with DBMS=XLSX (I've used DBMS=EXCEL, too). I get the error, "ERROR: DBMS type XLSX not valid for import."

Re: Importing Excel files in syntax without SAS/ACCESS

matt_beyers — Fri, 07 Sep 2018 21:53:00 GMT

I forgot to say, I have dozens of sheets in the Excel file and I didn't want to export to CSV or other. Based on these two replies, however, it seems I must do so and can't get around that without using SAS/ACCESS. I can write a VBA script to export them all to the same directory and then call them by referencing every CSV file in that directory in a list.

I do like the idea of creating an ODBC connection but want to make this easy on the users.

Re: Importing Excel files in syntax without SAS/ACCESS

Reeza — Fri, 07 Sep 2018 21:52:41 GMT

Honestly, I don't think they should sell SAS without SAS/ACCESS. Its a huge limitation and I don't know why they do that 😞