CODEWITHSUNDEEP
excelsas
user2173800
user2173800

Reputation: 97

Import Excel data into SAS whilst spreadsheet still open

I am doing a demo using SAS and Excel, and would like to be able to run a script in Base SAS that imports my on-screen Excel data.

Is this possible using native SAS routines, or DDE as a last resort? I am using Excel 2010 and SAS 9.3.

Upvotes: 1

Views: 4794

Answers (5)

Steve James
Steve James

Reputation: 1

I haven't been paying as close attention as I might, but it seems that for awhile it was not letting me do it but now it does allow me to use Proc Import with the file open in Excel. However what it also does is open another version of Excel with the spreadsheet open in read-only mode. I wish it wouldn't do that, which is how I came across this post. I don't know if it matters but I'm using the PC File Server engine to read the worksheet (DBMS=EXCELCS).

Note: http://support.sas.com/kb/15/547.html says that SAS can get different results if the file is open rather than closed. I had that experience that if the spreadsheet is open a column would be read as a different type from how it's read if the worksheet is closed.

Upvotes: 0

data _null_
data _null_

Reputation: 9109

You can use the FILELOCKS option. SAS will ignore any locks and proceed as usual. I use it with PROC IMPORT all the time.

options filelocks = ("&workbookdir" NONE);

Upvotes: 3

Allan Bowe
Allan Bowe

Reputation: 12691

Think DDE is your best bet here - the following code should do the trick (modify as appropriate):

filename xlSheet1 dde "Excel|C:\Location_of_excel_file\[demo.xlsx]Sheet1!R2C1:R40C2";
data mydataset;
   infile xlSheet1 dlm='09'x notab dsd missover;
   input var1:$15. var2:$15.;
run;

Upvotes: 1

Joe
Joe

Reputation: 63424

I think DDE would be the most obvious solution here for on-screen data. I'm not a DDE expert, but given that DDE operates by interacting with a currently running Excel process, it certainly seems tailor made to your results, despite its shortcomings and being effectively if not actually deprecated.

Another reasonable solution is to write a macro that exports your on-screen data to a CSV and then calls a SAS routine that imports that CSV. You can call SAS from VBA (like any other program), so it would be integrated into your Excel workbook (if this can be from many workbooks, put the macro in your personal workbook, or bring along the .xlsm if you need to use others' computers). This is probably what I'd do.

Finally, there is the SAS Add-in for Excel. http://support.sas.com/documentation/onlinedoc/addin/index.html I have no experience with it, but it's possible it will do what you need [depending on what the exact details are].

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

Base SAS alone cannot read from an Excel workbook. With the SAS Access to PC File Formats product (a separately licensed component), SAS can read from the most recently "saved" copy of the workbook:

proc import datafile='c:\temp\test4sas.xlsx'
     out=test
     replace;
run;

Note that SAS will not read directly from your Excel application, it reads from the workbook file itself. If you have "autosave" features turned on, the SAS dataset imported will contain whatever is currently "saved" in the workbook.

You might very well have SAS Access licensed on your site (most Windows sites have it as part of a bundle). To confirm, run this to see all the products licensed:

proc setinit;
run;

Upvotes: 2

Related Questions