05-04-2017 11:36 AM
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);
set summary ;
where Client_name = "&i.";
PROC EXPORT DATA= WORK.&j._output
OUTFILE= "\\<file location>\&i..xlsx"
SHEET = 'Raw';
%export(client 1,client 1);
05-04-2017 11:49 AM
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.
05-04-2017 11:55 AM
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.
is another post with the same issue however has no resolution
05-04-2017 11:58 AM
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.
02-27-2018 10:15 AM
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:
dbms=xlsx label replace;
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.
02-27-2018 12:48 PM