Hi all,
I'm running into this same problem. This link explains the issue but it says nothing about how to address it. 20923 - How REPLACE option works with PROC EXPORT and Excel files in SAS 9.1.
Other programs I run using proc export have no issues like this and exports just fine. This new program I am writing is giving me the error below and all I'm trying to do is a simple proc export:
**************************
PROC EXPORT DATA= work.test
OUTFILE= "C:\Documents and Settings\G137519\Desktop\PEAC\REPORTS\SOCO\Utilization_report\Dec_2013\data\SurgOne_Trend_12-04-2013_blank.xlsx"
DBMS=EXCEL REPLACE;
SHEET="Claims";
RUN;
**************************
WARNING: The target file may contain unmatched range name and sheet name.
WARNING: File _IMEX_.Claims.DATA does not exist.
WARNING: Table _IMEX_."Claims" has not been dropped.
**************************
The excel spreadsheet is saved as .XLSX and it includes the worksheet "Claims" in it. The export will only work if I exclude the Claims worksheet from the file. Otherwise it creates a new worksheet called "Claims1".
And I have tried changing the file extension to .XLS, and have also tried different options for "DBMS = " . Nothing seems to be working (as I want it - that is, proc export replacing any existing worksheets in my excel file), unless the worksheets are not present, but I want them present so I can add formatting and use the columns to make calculation on a different worksheet.
I am running SAS 9.2 and Excel 2010.
Any help would be greatly appreciated. Thanks,
Alex
OK, I got it to work, but it still doesn't explain why the regular method that's documented everywhere (and presented here by me and Swasw016) doesn't work. Frustrating when I know for a fact it works because I have done it before.
Based on Example 5 of this link: 42981 - Microsoft Excel files that have the .xlsx extension cannot be exported ,
I got a excel template from a previous assignment that I knew worked when getting SAS data exported to it. This file has extension .XLXS. I deleted all the tabs (worksheets) from this file and prepared it to receive the new sas data. So I created 3 new worksheets (which is what I needed for this exercise) and put in their names. Now I saved this modified .XLXS file with extension .XLS.
In my sas program I just changed the DBMS option to DBMS = XLS REPLACE; (as opposed to DBMS = EXCEL REPLACE;). I ran the program again and it worked.
Excel 2010 gives you a compatibility message asking you if you trust the data before opening (another hassle), but you can bypass that easily by just agreeing to open the file.
The problem is solved, but the question is not really answered. Why does proc export works in some cases and doesn't in others, is still lingering.
Any insights would still be much appreciated. Thanks,
Alex
OK, I picked this up again today, and I think I finally figured this out.
One thing I was not aware of (and please, let me know if I am off base here) is that proc export CREATES the excel file for you on that first run (I thought the excel file had to be there prior to running the proc export). This created file is what you can use as a shell for a template, so you can add new tabs for calculations, formatting, etc. Subsequent runs of proc export on the same program will REPLACE the data for the tabs that are getting exported.
So what I was doing wrong was that I created the excel file first (for this exercise, the excel file needed to have 2 tabs with exported SAS data called "data" and "claims", plus one more where I make calculations based on data from "data" and "claims"). So when I run proc export, sas tried to create "data" and "claims" but they have been already created. Therefore SAS creates "data1" and "claims1" and puts the data there.
The solution to this issue is to have SAS create that shell excel file first. After that do the necessary manipulations you want in this newly created excel file, and then rerun the sas program, and the proc export will REPLACE the data in the tabs being exported, and the tab with calculations based on "data" and "claims" tabs and formatting are updated accordingly.
Thanks!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.