BookmarkSubscribeRSS Feed
MC1985
Obsidian | Level 7

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

Martino Crippa
7 REPLIES 7
ballardw
Super User

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.

MC1985
Obsidian | Level 7

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.

 

Martino Crippa
lleheris
Calcite | Level 5

Hello

 

I have the same issue.

 

Does anyone know of any solution to this problem ?

 

Thank you in advance.

MC1985
Obsidian | Level 7

No, i'm so sorry. I create a smaller excel and bypass the errors

Martino Crippa
lleheris
Calcite | Level 5

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.

 

 

troopon
Calcite | Level 5

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.

avinashginjupal
Obsidian | Level 7

Check there is fix available on SAS Site for this:

 

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

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!

How to Concatenate Values

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.

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
  • 7 replies
  • 4780 views
  • 0 likes
  • 5 in conversation