topic Re: How to read Excel to SAS without DDE or SAS /Access Interface to PC files in SAS Programming

How to read Excel to SAS without DDE or SAS /Access Interface to PC files

QLi — Thu, 26 May 2011 20:28:34 GMT

I use Enterprise Guide 4.1. I want to read Exel to SAS. But I don't have DDE and SAS Access for Pc files.
So I cann't use PROC IMPORT or PROC ACCESS. Anyone know how to use INFILE statement to read?

Thanks in advance.

Qli

Re: How to read Excel to SAS without DDE or SAS /Access Interface to PC files

Cynthia_sas — Thu, 26 May 2011 22:09:41 GMT

Hi:
This blog has some useful information about EG and importing Excel files:
http://blogs.sas.com/sasdummy/index.php?/archives/258-Behind-the-scenes-importing-Excel-files-using-SAS-Enterprise-Guide.html

You also might find more help if you posted this question in the SAS Enterprise Guide forum:
http://support.sas.com/forums/forum.jspa?forumID=10

cynthia

Re: How to read Excel to SAS without DDE or SAS /Access Interface to PC files

SASKiwi — Thu, 26 May 2011 22:12:17 GMT

You can't read native Excel spreadsheets with INFILE. However if you save your Excel spreadsheets as a delimited file, for example CSV (comma delimited) you can then PROC IMPORT these files using DBMS = CSV or DLM, or an INFILE using the DLM option.

Re: How to read Excel to SAS without DDE or SAS /Access Interface to PC files

stateworker — Fri, 27 May 2011 13:34:17 GMT

Have to agree with SASKiwi - save your Excel spreadsheet as a delimited file (we prefer tab delimited, but obviously it will depend on the contents of your data as to what delimiter you choose). This is one example of when knowing what's available as a component of SAS but not having that feature makes one covetous 🙂

Re: How to read Excel to SAS without DDE or SAS /Access Interface to PC files

QLi — Fri, 27 May 2011 15:21:14 GMT

Thanks all,

I worked it out. Since DBMS type EXCEL not valid for import for Enterprise Guide, after changed to CSV file and worked well.