Morning to ALL,
i made a program that export some datasets to EXCEL ( from my SAS 9.4 and MS Excel 2016 on Windows 10 Machine -64bit), after 6 different sheets on same file SAS Corrupt the XLSX file and give me the <<ERROR: string is duplicated |><t>7</t>|>> in SAS Environment . SAS make a backup before replace the XLSX, but when i try to open file EXCEL say "corrupt file".
Any suggesst?
I need to enginering a program that make some elaborations and export over ten datasets in ten different sheets
this is my simple code(insert in a macroprogram):
proc export
data =&DS_INPUT.
dbms =xlsx
outfile ="&FILE."
replace;
sheet ="&SHEET_NAME.";
run;
thanks
MC
Show the entire code on how &sheet_name is built.
I would suspect something referencing the same sheet or something NOT acceptable in a sheet name.
Morning Ballardw,
macro is like that:
%MACRO EXPORT_XLS(DS_INPUT= , FILE= , SHEET_NAME=);
proc export
data =&DS_INPUT.
dbms =xlsx
outfile ="&FILE."
replace;
sheet ="&SHEET_NAME.";
run;
%MEND EXPORT_XLS;
the macro's calls are:
%LET XLS_OUT=C:\_Lavoro\_works\Sole24Ore\_Analisi\_20160513_AnalisiDatiUtentiDigital_per_dataQuality\xls\XLS_AnalisiDigitalV2.xlsx;
%EXPORT_XLS(DS_INPUT =colonneANADigital, FILE =&XLS_OUT., SHEET_NAME =COLONNE &DS_IN. );
%EXPORT_XLS(DS_INPUT =MISSING_VARS, FILE =&XLS_OUT., SHEET_NAME =MISSING );
%EXPORT_XLS(DS_INPUT =mail_tot, FILE =&XLS_OUT., SHEET_NAME =MAIL SUMMARY );
%EXPORT_XLS(DS_INPUT =mail_per_month, FILE =&XLS_OUT., SHEET_NAME =MAIL TEMPO );
%EXPORT_XLS(DS_INPUT =mail_per_tipo_utente, FILE =&XLS_OUT., SHEET_NAME =MAIL per TIPOUTENTE );
%EXPORT_XLS(DS_INPUT =mail_per_anno_tipo_utente, FILE =&XLS_OUT., SHEET_NAME =MAIL TIPOUTENTE TEMPO );
%EXPORT_XLS(DS_INPUT =mail_per_anno_duplicate, FILE =&XLS_OUT., SHEET_NAME =MAIL DUPLICATE );
%EXPORT_XLS(DS_INPUT =mail_duplicate_tipo_utente, FILE =&XLS_OUT., SHEET_NAME =MAIL DUP TIPO UTENTE );
%EXPORT_XLS(DS_INPUT =dup_ultimi_due, FILE =&XLS_OUT., SHEET_NAME =MAIL DUPLICATE ULTIMI 2);
%EXPORT_XLS(DS_INPUT =mail_per_annomeseUTILIZZABILI, FILE =&XLS_OUT., SHEET_NAME =MAIL UTILIZZABILI );
%EXPORT_XLS(DS_INPUT =mail_utils_prod, FILE =&XLS_OUT., SHEET_NAME =MAIL UTIL TIPO PROD );
%EXPORT_XLS(DS_INPUT =mail_err_utils_prod, FILE =&XLS_OUT., SHEET_NAME =MAIL ERR TIPO PROD );
%EXPORT_XLS(DS_INPUT =mail_canc_per_annomese, FILE =&XLS_OUT., SHEET_NAME =MAIL CANCELLATE );
%EXPORT_XLS(DS_INPUT =indirizzi, FILE =&XLS_OUT., SHEET_NAME =INDIRIZZI );
%EXPORT_XLS(DS_INPUT =cf, FILE =&XLS_OUT., SHEET_NAME =CF );
%EXPORT_XLS(DS_INPUT =freq_norm_cf, FILE =&XLS_OUT., SHEET_NAME =NORM CF );
%EXPORT_XLS(DS_INPUT =piva, FILE =&XLS_OUT., SHEET_NAME =PIVA );
%EXPORT_XLS(DS_INPUT =freq_norm_piva, FILE =&XLS_OUT., SHEET_NAME =NORM PIVA );
%EXPORT_XLS(DS_INPUT =freq_professione, FILE =&XLS_OUT., SHEET_NAME =PROFESSIONE );
%EXPORT_XLS(DS_INPUT =SETTORE, FILE =&XLS_OUT., SHEET_NAME =SETTORE );
%EXPORT_XLS(DS_INPUT =privacy, FILE =&XLS_OUT., SHEET_NAME =PRIVACY );
%EXPORT_XLS(DS_INPUT =privacy_dis, FILE =&XLS_OUT., SHEET_NAME =PRIVACY DISCLAIMER );
%EXPORT_XLS(DS_INPUT =privacy_mail, FILE =&XLS_OUT., SHEET_NAME =PRIVACY MAIL );
%EXPORT_XLS(DS_INPUT =privacy_so, FILE =&XLS_OUT., SHEET_NAME =PRIVACY SOCIETA );
%EXPORT_XLS(DS_INPUT =privacy_pattern, FILE =&XLS_OUT., SHEET_NAME =PRIVACY PATTERN );
%EXPORT_XLS(DS_INPUT =pattern_priva_temp, FILE =&XLS_OUT., SHEET_NAME =PATTERN TEMP );
%EXPORT_XLS(DS_INPUT =cell, FILE =&XLS_OUT., SHEET_NAME =CELLULARE );
%EXPORT_XLS(DS_INPUT =cell_norm, FILE =&XLS_OUT., SHEET_NAME =CELLULARE NORM );
%EXPORT_XLS(DS_INPUT =tel, FILE =&XLS_OUT., SHEET_NAME =TELEFONO );
%EXPORT_XLS(DS_INPUT =tel_norm, FILE =&XLS_OUT., SHEET_NAME =TELEFONO NORM );
the restrictions are:
1. sas name less equal 32 char
2. excel sheet name les equale 31
the error appear in a random way.
thank you very much.
let me know if you have suggest.
Hello
I have the same issue.
Does anyone know of any solution to this problem ?
Thank you in advance.
No, i'm so sorry. I create a smaller excel and bypass the errors
Thank you for your reply.
It isn't a critical error in my process but I keep hoping that one day someone will find a potential solution.
Did you ever find a resolution to this? I am encountering a similar problem but can't find the solution anywhere.
Like you the error sometimes occurs when I run the script and sometimes doesn't. I am also doing a macros export to an excel file (xlsx) so problem is likely the same. My excel corrupts too. I can get into the excel but other sheets on there have had columns moved and formats changed.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.