Proc Export - Error: String is duplicated

[troopon]

Hi

I have an export step at the end of my code which exports to a excel document on a specific sheet.

Within that excel doc there is a second sheet which links to this data.

When I initially run (and on most subsequent runs) it all works fine however sometimes when I run I get an error message saying "Error: String is duplicated" plus some symbols in brackets.

If I get this error message it seems to still export data but also corrupts the excel file. I can go into the excel file but column names have moved, formats changed etc which shouldn't happen. 

Does anyone know what this error message means and how to fix the issue. Odd how it only happens sometimes (though seems to happen more often if I run the full script rather than bit by bit). Code is below. Currently I am only testing it for one client but eventually this will be rolled out to run across multiple clients. I use SAS Studio but don't think this is important for this issue.

%macro Export (i,j);
data &j._output;
set summary ;
where Client_name = "&i.";
run;

PROC EXPORT DATA= WORK.&j._output
OUTFILE= "\\<file location>\&i..xlsx"
DBMS=xlsx
REPLACE;
SHEET = 'Raw';
RUN;

%mend Export;

%export(client 1,client 1);

[Reeza]

This is an Excel error? I haven't seen that error but I noticed that the SHEETNAME option is fixed. Im assuming you're writing to a template? 

It may be possible that there's something in a specific report that's causing the issue. 

Rather than create the subset you can place the WHERE in your PROC EXPORT. 

[troopon]

Hi Reeza

No it isn't an excel error. This error appears in the SAS log but for some reason doesn't always occur. It's not an error I have seen (or anyone in my office I have asked has). It's an odd error as it doesn't seem to relate to the statement in anyway. Usually SAS errors have some sort of clue in.

This makes me wonder if it's something to do with space because it is a lengthy script although the actual export is only 12 rows for this particular client.

https://communities.sas.com/t5/General-SAS-Programming/PROC-EXPORT-lt-lt-ERROR-string-is-duplicated-...

is another post with the same issue however has no resolution

[Reeza]

I would contact tech support. If needed the developers may be able to say how that error is generated. 

[Reeza]

Do you actually have spaces in your i/j variables? 

[troopon]

In this case no. i and j are both 1 word. I have called it client 1 up top instead of the actual client. There are also no odd characters within the client name. Just letters.

When I add additional clients, some will have spaces but this isn't an issue in this instance.

[titus]

I realize this question is almost a year old but I've been having this issue for a couple weeks and finally found something that worked for me. I have an xlsx template saved on the server with a hidden sheet simply called "Data" and then a few other formatted sheets in the template with pivots based off the Data tab. I was continually receiving the string was duplicated error when running this code:

proc export
data=work.Master
outfile="&xFile."
dbms=xlsx label replace;
sheet="Data";
run;

What finally made the difference was when I deleted all rows except the header from the Data tab on my template. Everything is working as intended now. 🙂 Not sure this would be the solution for every case, but it at least solved it for me.

[Satish_Parida]

We had a similar kind of issue, we use template to create XML files which we open in excel. Some times due to some reason they produce some dummy character at the and, which will cause the XML not opening in Excel. But we can never recreate the issue with same data.

[chazbash]

Hi all,

I was having the same issue and simply switched to double quotes on the sheet name.  I noticed also in this example.   

[avinashginjupal]

Check there is fix available on SAS Site for this:

http://support.sas.com/kb/62/655.html