BookmarkSubscribeRSS Feed
Swade016
Calcite | Level 5
I'm getting an error on 9.2 during Proc Export that I've never seen before in any previous version.

ERROR: The target file contains unmatched range name and sheet name. You may use other name or remove the unmatched range/sheet in the file.

This is done while performing an export to a specific sheet in Excel and utilizing the REPLACE option.

Thoughts?
5 REPLIES 5
deleted_user
Not applicable
Check the spelling of your range and sheet, and verify you don't have a littoral. Check too that the case of the name matches. When I get issues like this, I go to Excel, highlight the name, copy it and paste it into my SAS code.

If you've not seen the error in previous versions, have you run the code in 9.1.3 successfully? Or just never met this particular error?

Kind regards

David
Swade016
Calcite | Level 5
It's done using Macros, and the sheet and file names haven't changed (nor the code) since version 2005.

It's ran successfully in 8.2, 9.1.3, 9.1.3SP4, and sometimes in 9.2. The only thing I can think of is that 9.2 doesn't like the replace option.

Here is the code:

Proc Export
Data = &Data
DBMS = Excel
Outfile = "&Location"
Replace;
Sheet = "&Sheet";
Quit;

Example &MasterFile = H:\external\Customer services\IDR Report Repository\Archer Daniels Midland\7CIT1Z71011.xls

Example Sheet = 14DEC06_16JAN07
avbraga
Calcite | Level 5

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

avbraga
Calcite | Level 5

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

avbraga
Calcite | Level 5

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!

sas-innovate-2024.png

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.

 

Register now!

What is Bayesian Analysis?

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 6009 views
  • 0 likes
  • 3 in conversation