topic Re: Excel with Custom Property Names in ODS and Base Reporting

Excel with Custom Property Names

jimbarbour — Thu, 03 Nov 2022 22:30:00 GMT

I want to create an Excel spreadsheet with ODS. I want to create a property with a custom name, MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Name and assign it a value of either "External Label" or "Internal Label". This is a company specific requirement that we specify the sensitivity of all documents.

I know how to create the standard properties like Author, Comments, etc that go into the core.xml part of an xlsx file, but I don't know how to create a custom property name that would go into the custom.xml part of an xlsx file. The screen print below is of the docProps folder within an xlsx file that has had the custom property manually set. The XML below that is the contents of custom.xml.

I've Googled some, but everything I'm finding relates to just standard properties like Author. Can anyone point me in the right direction here?

Jim

<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties">
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Enabled" pid="2" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>true</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SetDate" pid="3" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>2022-11-02T22:36:32Z</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Method" pid="4" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>Privileged</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Name" pid="5" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>External Label</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SiteId" pid="6" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>db05faca-c82a-4b9d-b9c5-0f64b6755421</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ActionId" pid="7" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>006e8ed9-6489-4393-a145-7272eed3aa2d</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ContentBits" pid="8" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>0</vt:lpwstr>
</property>
</Properties>

Re: Excel with Custom Property Names

Kurt_Bremser — Fri, 04 Nov 2022 07:41:08 GMT

I think you will have to first disassemble the .xlsx file (after creation with ODS), then add the text to the custom.xml file, and then reassemble the .xlsx.

Re: Excel with Custom Property Names

jimbarbour — Fri, 04 Nov 2022 15:52:16 GMT

Hello, Kurt,

That's along the lines of what I was thinking, that I'd have to create the XML with a Data step and add it to the xlsx file inside the docProps folder. It shouldn't be too hard, but I was hoping that there was something that SAS already had in place.

I wonder if @Cynthia_sas might have any insight here.

Jim

Re: Excel with Custom Property Names

ballardw — Fri, 04 Nov 2022 16:23:47 GMT

You don't mention how you are creating the Excel file so I'm not sure this is at all helpful.

The ODS EXCEL option CATEGORY='text' will place stuff into the Custom Properties of the file. I just can't see how to set the name, it seems to get a generic category tag as the name and the text string is the value.

Might be helpful as you could have the string value in place in the properties and be able to reduce the amount of editing needed.

Re: Excel with Custom Property Names

Tom — Fri, 04 Nov 2022 18:09:23 GMT

It does not look like SAS creates that XML file. At least PROC EXPORT does not.

So just add it.

%let filename=~/class.xlsx;
proc export data=sashelp.class file="&filename" replace dbms=xlsx;
run;
data _null_;
  file "&filename" zip member="docProps/custom.xml";
  infile cards truncover;
  input line $char200.;
  len=length(line);
  put line $varying200. len ;
cards4;
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
-<Properties xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes" xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties">
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Enabled" pid="2" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>true</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SetDate" pid="3" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>2022-11-02T22:36:32Z</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Method" pid="4" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>Privileged</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Name" pid="5" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>External Label</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SiteId" pid="6" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>db05faca-c82a-4b9d-b9c5-0f64b6755421</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ActionId" pid="7" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>006e8ed9-6489-4393-a145-7272eed3aa2d</vt:lpwstr>
</property>
-<property name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ContentBits" pid="8" fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}">
<vt:lpwstr>0</vt:lpwstr>
</property>
</Properties>
;;;;

Re: Excel with Custom Property Names

jimbarbour — Fri, 04 Nov 2022 20:19:04 GMT

Hi, @ballardw

Sorry. I should have said more about how I'm creating the Excel spreadsheet. I'm using ODS Excel. Here's a simple example, below. I tried setting Text= in the ODS Excel options, but I didn't see where it set anything after it ran. I may be doing it wrong (syntax or something); I've not used this feature heretofore. It ran successfully but I didn't see any parameter set in the Custom Properties when I examined the resultant xlsx file, and I didn't see a custom.xml file inside the docProps folder inside the xlsx file.

Jim

DATA	WORK.Test_Data;
	INFILE	DATALINES4 	DSD	DLM='09'X  missover;
	INPUT	Animal 		$
			Legs		
			Best_Sense	$
			;
DATALINES4;
Dog	4	Smell
Eagle	2	Sight
Bear	4	Smell
Rabbit	4	Hearing 
;;;;
RUN;

ODS	EXCEL	FILE	=	"&File_Name"
	AUTHOR			=	'Jim Barbour via SAS'
	CATEGORY		=	"Iteration &Iteration"
	COMMENTS		=	'This is a test to set the sensitivity to External'
/*	TEXT			=	"MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Name	=	""External Label"""	*/
	;

PROC	PRINT	DATA=	Test_Data;
RUN;

ODS EXCEL CLOSE;

Re: Excel with Custom Property Names

ballardw — Fri, 04 Nov 2022 21:29:14 GMT

Maybe a configuration issue. How did you check on the properties?

When I open the file in Libre Office (I don't have Microsoft Office programs installed on this machine) and open the Properties from the File>Properties menu I get the following. The "property name" bit is the result of trying a number of ways that might have "tricked" the app into setting a desired property name but no joy.

Re: Excel with Custom Property Names

jimbarbour — Sat, 05 Nov 2022 00:09:52 GMT

@ballardw ,

I check by:

a) looking at the info panel of Excel and then opening up the drop down for "advanced properties" and

b) looking at the xml files embedded inside the xlsx file

Setting "Text=xxxx" does work... sort of. It doesn't affect the custom properties though. Text= just puts a line of inside your Excel worksheet. The below program produces the screen printed output, below. Note the value in cell A1.

Jim

ODS	EXCEL	FILE		=	"&File_Name"
	TITLE				=	'Animal Senses and Locomotion'
	AUTHOR				=	'Jim Barbour via SAS'
	CATEGORY			=	"Iteration &Iteration"
	COMMENTS			=	'This is an attempt to set the sensitivity to External Label'
	STATUS				=	'Test'
	KEYWORDS			=	'Custom Properties'
	OPTIONS(
		SHEET_NAME		=	'Animal_Senses_Locomotion'
		GRIDLINES		=	'ON'
		TAB_COLOR		=	'YELLOW'
		)
	TEXT				=	"Hello Ballard, this is text"
	;

PROC	PRINT	DATA=	Test_Data;
RUN;

ODS EXCEL CLOSE;

Re: Excel with Custom Property Names

jimbarbour — Sat, 05 Nov 2022 00:21:10 GMT

@Tom ,

Thank you! This is excellent.

Now, I've discovered another complication. The .rels file and the [Content_Types].xml files have to be updated to include the fact that a custom.xml now exists. However, when I try to read in the .rels file which is in the _rels folder, SAS says that it can't find the file. I'm suspicious that the period in the .rels file may be throwing SAS off, but I'm not sure. If I use the same code to try to read a different file in the very same xlsx, it works just fine. Code is below. Log is below that.

Do you have any idea what's going on here?

Jim

File is clearly present inside the _rels folder inside the xlsx file:

data	WORK.Rels_XML;
	DROP	_:;
	infile	"&File_Name"	zip	member="_rels&Slash..rels"	LENGTH=SAS_Length;
*	infile	"&File_Name"	zip	member="docProps&Slash.custom.xml"	LENGTH=SAS_Length;
	INPUT	Line	$VARYING32767.	SAS_Length;
	_Position	=	FIND(Line, '</relationships>', 'i');
	IF	_Position	THEN
		DO;
			SUBSTR(Line,_Position)	=	'<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/custom.xml"/></Relationships>';
		END;
run;

ERROR: Entry _rels\.rels in zip file 
       C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS\Excel_External_Label_6.xlsx does not exist.
ERROR: Physical file does not exist, _rels\.rels.

Re: Excel with Custom Property Names

Tom — Sat, 05 Nov 2022 02:12:52 GMT

What are you trying to read it with?

Works file for me just using a data step.

132  data _null_;
133    infile "&fname" zip member="_rels/.rels" ;
134    input;
135    put _infile_;
136  run;

NOTE: The infile "xxx/class.xlsx" is:
      (system-specific pathname),
      (system-specific file attributes)

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Relationships xmlns="http://schemas.openxmlformats.org/package/2006/relationships">
   <Relationship Id="rId1" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/officeDocument" Target="xl/workbook.xml"/>
   <Relationship Id="rId2" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/extended-properties" Target="docProps/app.xml"/>
   <Relationship Id="rId3" Type="http://schemas.openxmlformats.org/package/2006/relationships/metadata/core-properties" Target="docProps/core.xml"/>
</Relationships>
NOTE: 6 records were read from the infile (system-specific pathname).
      The minimum record length was 16.
      The maximum record length was 149.
NOTE: DATA statement used (Total process time):
      real time           0.09 seconds
      cpu time            0.00 seconds

Re: Excel with Custom Property Names

Tom — Sat, 05 Nov 2022 02:16:29 GMT

Why did you use \ instead of / in the member name?

Re: Excel with Custom Property Names

jimbarbour — Sat, 05 Nov 2022 14:25:56 GMT

@Tom, I'm running on a Windows 2016 server. Normally paths use a back slash on Windows. Maybe it should be a forward slash inside a zip archive? That would be counterintuitive that they would not follow the OS, but I suppose it's worth a try. I it will try later today when I get a chance.

Jim

Re: Excel with Custom Property Names

jimbarbour — Sat, 05 Nov 2022 14:29:03 GMT

@Tom, I'm reading the xlsx file with the Data step I posted earlier. I'll post it below for ease of reference.

Jim

data	WORK.Rels_XML;
	DROP	_:;
	infile	"&File_Name"	zip	member="_rels&Slash..rels"	LENGTH=SAS_Length;
*	infile	"&File_Name"	zip	member="docProps&Slash.custom.xml"	LENGTH=SAS_Length;
	INPUT	Line	$VARYING32767.	SAS_Length;
	_Position	=	FIND(Line, '</relationships>', 'i');
	IF	_Position	THEN
		DO;
			SUBSTR(Line,_Position)	=	'<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/custom.xml"/></Relationships>';
		END;
run;

Re: Excel with Custom Property Names

Tom — Sat, 05 Nov 2022 16:00:25 GMT

You can ask SAS to lookup the names.

ods excel file='c:\downloads\class.xlsx' ;
proc print data=sashelp.class;
run;
ods excel;
filename xlsx zip 'c:\downloads\class.xlsx' ;

data xlsx_members ;
  did = dopen('xlsx');
  do num=1 to dnum(did);
    length name $200;
    name = dread(did,num);
    output;
  end;
  did=dclose(did);
  drop did;
run;

proc print;
run;

Obs    num    name

  1      1    [Content_Types].xml
  2      2    xl/worksheets/sheet1.xml
  3      3    xl/worksheets/_rels/sheet1.xml.rels
  4      4    xl/worksheets/sheet2.xml
  5      5    xl/worksheets/_rels/sheet2.xml.rels
  6      6    xl/workbook.xml
  7      7    xl/styles.xml
  8      8    xl/sharedStrings.xml
  9      9    xl/theme/theme1.xml
 10     10    docProps/app.xml
 11     11    xl/_rels/workbook.xml.rels
 12     12    docProps/core.xml
 13     13    _rels/.rels

Re: Excel with Custom Property Names

jimbarbour — Sun, 06 Nov 2022 03:15:12 GMT

@Tom,

Changing to a forward slash inside the Zip archive worked. I can now read the _rels/.rels file.

I'm now having what appears to be a problem with the Zip engine on a SAS Filename. I have to update a number of the xml files in order to keep everything in sync:

_rels/.rels
docProps/app.xml
docProps/custom.xml
[Content_Types].xml

Everything runs fine, but when I try to open things in Excel, I get a message that there's a problem. Recovery fails.

When I actually look at the contents of the xlsx file using WinZip, I typically get a message that there's a CRC checksum mismatch. Exactly which of the xml components gets this message varies with the order I create the xml components. It shouldn't matter what order I create the xml components. It seems to me that the Zip engine shouldn't be doing this.

I'm running SAS 9.4 M6 on a Windows Server 2016 Standard (64 bit) machine. My program is below. The order of xml component creation is controlled by the order in the DATALINES4.

Would you happen to have any insight here?

Jim

Detailed messages. I note that components that I'm not even touching are getting their CRC check sums messed up. I think that indicates a problem with the Zip engine.

Extracting file: C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS\Class_3.ZIP
Extracting to "C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS\Class_3\"
Use Path: yes   Overlay Files: no
Extracting xl\worksheets\sheet1.xml
Extracting xl\sharedStrings.xml
Extracting docProps\core.xml
Extracting xl\styles.xml
Central and local directory mismatch for file "xl/styles.xml" (crc-32 - local: 1148BA33 hex  central: 3E9D0B61 hex).
Extracting docProps\app.xml
Extracting _rels\.rels
Central and local directory mismatch for file "_rels/.rels" (crc-32 - local: 1148BA33 hex  central: 52D5847E hex).
Extracting xl\_rels\workbook.xml.rels
Unable to find the local header for xl\_rels\workbook.xml.rels.
Extracting xl\workbook.xml
Central and local directory mismatch for file "xl/workbook.xml" (crc-32 - local: 721EAEAE hex  central: 4B8E0F38 hex).
Severe Error: Local and central CRC values don't match.

Program:

%Time_Stamp(START);

**	Set slash to back slash for Windows or forward slash for LINUX/UNIX	**;
%LET	Slash		=	%SYSFUNC(IFC(%EVAL(&SYSSCP = WIN),\,/));

*%LET	Iteration	=	0;

%IF	%SYMEXIST(Iteration)	%THEN
	%DO;
		%LET	Iteration	=	%EVAL(&Iteration + 1);
	%END;
%ELSE
	%DO;
		%LET	Iteration	=	1;
	%END;

%PUT	&Nte1  Running Iteration &Iteration;

%LET	Path		=	C:\Users\jbarbou6\Documents\SAS\Pgm\Training\Excel_Properties_ODS;
*%LET	Path		=	I:\commercial\user\jbarbou3\ODS_Excel;
%LET	File_Name	=	&Path.&Slash.Class_&Iteration..xlsx;

**	Create an xlsx file with Proc Export	**;
PROC	EXPORT	DATA=sashelp.class	FILE="&File_Name"	REPLACE	DBMS=xlsx;
RUN;

FILENAME	XL_File	ZIP	"&File_Name";

**------------------------- Relationships --------------------------------**;
**	Read in the .rels relationships file, add a reference to custom.xml, and write out a SAS data set containing the modified .rels xml	**;
DATA	WORK.Rels_XML;
	DROP	_:;
	INFILE	XL_File(_rels/.rels)	LENGTH=SAS_Length;
	INPUT	Line	$VARYING32767.	SAS_Length;
	_Position	=	FIND(Line, '</relationships>', 'i');
	IF	_Position	THEN
		DO;
			PUTLOG	"&Nte1  Adding custom.xml entry to spreadsheet iteration &Iteration";
			SUBSTR(Line,_Position)	=	'<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/custom.xml"/></Relationships>';
		END;
RUN;

**------------------------- Content Types --------------------------------**;
**	Read in the Content Types XML file, add a reference to custom.xml, and write out a SAS data set containing the modified Content Types XML.	**;
DATA	WORK.Content_Types_XML;
	DROP	_:;
	INFILE	XL_File([Content_Types].xml)	LENGTH=SAS_Length;
	INPUT	Line	$VARYING32767.	SAS_Length;
	_Position	=	FIND(Line, '</Types>', 'i');
	IF	_Position	THEN
		DO;
			PUTLOG	"&Nte1  Adding custom.xml entry to spreadsheet iteration &Iteration";
			SUBSTR(Line,_Position)	=	'<Override PartName="/docProps/custom.xml" ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml"/></Types>';
		END;
RUN;

**--------------------------- Custom.XML --------------------------------**;
**	Create a SAS data set that contains the custom XML that will set the label to either External or Internal	**;
DATA	WORK.Custom_XML;
	INFILE	DATALINES4	LENGTH=SAS_Length;
	INPUT	Line	$VARYING32767.	SAS_Length;
DATALINES4;
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/custom-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="2" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Enabled"><vt:lpwstr>true</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="3" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SetDate"><vt:lpwstr>2022-11-05T21:48:26Z</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="4" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Method"><vt:lpwstr>Privileged</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="5" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_Name"><vt:lpwstr>External Label</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="6" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_SiteId"><vt:lpwstr>db05faca-c82a-4b9d-b9c5-0f64b6755421</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="7" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ActionId"><vt:lpwstr>6d6ffa67-7505-4457-b8c1-4592fb83e3c9</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="8" name="MSIP_Label_320f21ee-9bdc-4991-8abe-58f53448e302_ContentBits"><vt:lpwstr>0</vt:lpwstr></property></Properties>
;;;;
RUN;

**--------------------------- App.XML --------------------------------**;
**	Create a SAS data set that contains the App.XML that will correspond to the rest of the XML	**;
DATA	WORK.App_XML;
	INFILE	DATALINES4	LENGTH=SAS_Length;
	INPUT	Line	$VARYING32767.	SAS_Length;
DATALINES4;
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Properties xmlns="http://schemas.openxmlformats.org/officeDocument/2006/extended-properties" xmlns:vt="http://schemas.openxmlformats.org/officeDocument/2006/docPropsVTypes"><Application>Microsoft Excel</Application><DocSecurity>0</DocSecurity><ScaleCrop>false</ScaleCrop><HeadingPairs><vt:vector size="4" baseType="variant"><vt:variant><vt:lpstr>Worksheets</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant><vt:variant><vt:lpstr>Named Ranges</vt:lpstr></vt:variant><vt:variant><vt:i4>1</vt:i4></vt:variant></vt:vector></HeadingPairs><TitlesOfParts><vt:vector size="2" baseType="lpstr"><vt:lpstr>Class_25</vt:lpstr><vt:lpstr>Class_25</vt:lpstr></vt:vector></TitlesOfParts><Company>SAS Institute Inc</Company><LinksUpToDate>false</LinksUpToDate><SharedDoc>false</SharedDoc><HyperlinksChanged>false</HyperlinksChanged><AppVersion>16.0300</AppVersion></Properties>
;;;;
RUN;

**	Update/add the XML files to the xlsx file which is a Zip archive	**;
DATA	_NULL_;
	LENGTH	Mem_Name	$128;

	INFILE	DATALINES4;
	INPUT	Mem_Name	$;
	FILE	XL_File	MEMVAR=Mem_Name;
	PUTLOG	"&Nte1  1.  "	_N_=  Mem_Name=;

	IF	Mem_Name	=	'docProps/custom.xml'	THEN
		DO;
			**	Create the custom.xml file from the SAS data set that contains the custom XML that will set the label to either External or Internal	**;
			PUTLOG	"&Nte1  Processing "		Mem_Name;
			DO	UNTIL	(End_Custom);	
				SET	WORK.CUSTOM_XML				END=End_Custom;
				Leng	=	length(line);
				PUT	Line	$Varying32767.		Leng;
			END;
		END;

	IF	Mem_Name	=	'docProps/app.xml'	THEN
		DO;
			**	Create the app.xml file from the SAS data set that contains the App XML	**;
			PUTLOG	"&Nte1  Processing "		Mem_Name;
			DO	UNTIL	(End_App);	
				SET	WORK.App_XML				END=End_App;
				Leng	=	length(line);
				PUT	Line	$Varying32767.		Leng;
			END;
		END;

	IF	Mem_Name	=	'_rels/.rels'			THEN
		DO;
			**	Write the modified .rels xml to the .rels file	**;
			PUTLOG	"&Nte1  Processing "		Mem_Name;
			DO	UNTIL	(End_Rels);
				SET	WORK.Rels_XML				END=End_Rels;
				Leng	=	length(line);
				PUT	Line	$Varying32767.		Leng;
			END;
		END;

	IF	Mem_Name	=	'[Content_Types].xml'	THEN
		DO;
			**	Write the modified Content Types XML to the Content Types XML file	**;
			PUTLOG	"&Nte1  Processing "		Mem_Name;
			DO	UNTIL	(End_Content);
				SET	WORK.Content_Types_XML		END=End_Content;
				Leng	=	length(line);
				PUT	Line	$Varying32767.		Leng;
			END;
		END;

DATALINES4;
_rels/.rels
docProps/custom.xml
docProps/app.xml
[Content_Types].xml
;;;;
RUN;

%Time_Stamp(STOP);

Re: Excel with Custom Property Names

Tom — Sun, 06 Nov 2022 17:45:01 GMT

Break the process into steps to figure out which one is causing the issue.

Expand the XLSX file into actual files (using UNZIP).

Rebuild it into a new file (using ZIP).

Does it still work? If not then perhaps the changes are not compatible with what Excel wants.

Then introduce making the changes.

Try making the changes without SAS.

Try making the changes with SAS, but only one file at a time (perhaps it is the MEMNAME= file option that is confused).

Then try making the changes in the original XLSX file. Perhaps it is the ZIP engine.

Re: Excel with Custom Property Names

jimbarbour — Mon, 07 Nov 2022 06:24:00 GMT

Break the process into steps to figure out which one is causing the issue.

That was the first thing I tried. No luck. No combination of run order of the steps separated out individually works. Each combination of steps all produce one kind of error or another, typically the CRC check failure but sometimes "file is not a zip file" also occurs. In fact, the reason I combined all the update steps and used a Memvar was to see if somehow the CRC error might be resolved if all updates/adds were in one Data step. No luck; neither individual Data steps nor a combined all-in-one Data step worked.

Expand the XLSX file into actual files (using UNZIP).

Rebuild it into a new file (using ZIP).

Does it still work? If not then perhaps the changes are not compatible with what Excel wants.

I can't expand the xlsx file if updates have been applied. Well, I can open it, but many of the component XML files inside get a CRC error and cannot be opened.

However, I created a xlsx file using the SASHELP.Class data set and Proc Export. I unzipped everything and manually applied updates to the XML using ordinary Windows Notepad. After the manual updates, I zipped everything up. Everything worked fine. The resultant xlsx file opens in Excel, displays the Class data correctly, and has the "External" label applied correctly. This would seem to confirm that the SAS zip engine is having some kind of problem.

I've also, using SAS, made one change at a time. If I do only one of the four changes, it works fine at least in the sense that I can see the expected XML in the zip archive. However, if I try to do multiple updates, no matter what order I do them in, I get CRC errors. Individually, they work fine. Taken together, Excel cannot open the file, and I can't unzip the XML components inside the xlsx due to CRC errors.

Everything seems to be pointing to a SAS zip engine error.

Jim

Edit: I have a sneaking suspicion that SAS may be just updating the files inside the Zip archive without re-calculating the checksums, possibly causing the apparent out-of-sync type messages I'm getting that state that the central and local directory have CRC-32 check sums that are different. I haven't found it yet, but if there were a way to force the SAS Zip engine to recalculate all the CRC-32 check sums, that might correct the problem. I'm sure that WinZip calculates all the CRC-32 check sums afresh when I zip everything up.

Re: Excel with Custom Property Names

jimbarbour — Mon, 07 Nov 2022 16:34:09 GMT

I suppose, since it at least appears to be a SAS Zip engine issue, that I ought to open a new thread. I can't really address the ODS issue -- which I think is essentially solved -- until the Zip issue is resolved.

Jim

Re: Excel with Custom Property Names

Tom — Mon, 07 Nov 2022 17:03:36 GMT

@jimbarbour wrote:

I suppose, since it at least appears to be a SAS Zip engine issue, that I ought to open a new thread. I can't really address the ODS issue -- which I think is essentially solved -- until the Zip issue is resolved.

Jim

If you have an reproducible issue with the ZIP engine then open a ticket with SAS support and see if they can figure out waht is happening. They might know of a work around, or at least it gets on their list of known issues and so might get fixed in the future.

Re: Excel with Custom Property Names

jimbarbour — Mon, 07 Nov 2022 17:22:26 GMT

@Tom wrote:

If you have an reproducible issue with the ZIP engine then open a ticket with SAS support and see if they can figure out waht is happening. They might know of a work around, or at least it gets on their list of known issues and so might get fixed in the future.

I just created a thread specific to the Zip engine issue. I'm hoping that someone may have already encountered this and already have a work-around. If not, then opening a support ticket is definitely the next step.

Jim