@Tom, @ballardw, @ChrisHemedinger, and @Kurt_Bremser
If it is of continued interest, I wrote a macro to set the custom properties in Excel. I was able to get around the CRC-32 checksum issue that I was having by extracting all the embedded xml files inside the xlsx file, changing the xml as needed, and then zipping all the updated xml into a replacement xlsx file.
My macro sets the "Sensitivity" custom property to either Internal or External. It requires a file_name, path, and label value (Internal or External). Example: %set_sensitivity_label(&Path, &File_Name, Label=External);
Code is below. There are also two subordinate macros, one to extract the xml members from the original xlsx and another to add the updated xml members to the new xlsx.
I hope it is of interest. The custom property itself may not be interesting, but an example of how to extract, update, and replace xlsx components might be useful.
Jim
/*------------------------------------------------------------------------------*/
/* Macro: set_sensitivity_label
/* Author: Jim Barbour
/* Date: 08 November 2022
/* Info: This macro will set the sensitivity label on an Excel xlsx file to
/* either "Internal" or "External".
/*
/* The sensitivity label is an Excel custom property that is required
/* for all Excel xlsx files. This macro will set the label to either
/* "Internal" or "External" based on the arguments passed in when the
/* macro is called. If the Excel xlsx file does not exist, this macro
/* will create a skeleton file for you. When you populate the file
/* with actual data, make sure that you do not delete or overlay the
/* skeleton. For example, on Proc Export, code the REPLACE option.
/* If you cannot run without deleting or overlaying the skeleton, then
/* run this macro *after* you populate your Excel xlsx file.
/* If this macro creates the Excel xlsx file, a sheet named "Placehoder"
/* will be created. You may delete this sheet at your convenience.
/*------------------------------------------------------------------------------*/
/* CHANGE LOG
/*------------------------------------------------------------------------------*/
/* Name: Jim Barbour Date: 08 November 2022
/* Info: Original implementation.
/*------------------------------------------------------------------------------*/
%MACRO set_sensitivity_label(Path /* The Path to your Excel xlsx file. */,
File_Name /* The name of your Excel xlsx file. */,
Label=Internal /* Either Internal or External. */,
Debug=NO /* YES=on NO=off for debug logging. */,
Width=42 /* Sets the display width for logging. */
);
%LOCAL Slash;
%LOCAL Path_File;
%LOCAL lCmnt;
%LOCAL i;
%LOCAL Nte1;
%LOCAL Nte2;
%LOCAL Warn1;
%LOCAL Warn2;
%LOCAL Err1;
%LOCAL Err2;
%LET Nte1 = NOTE:;
%LET Nte2 = NOTE-;
%LET Warn1 = WARNING:;
%LET Warn2 = WARNING-;
%LET Err1 = ERROR:;
%LET Err2 = ERROR-;
OPTION DLCREATEDIR;
%IF %QUPCASE(&Debug) = YES %THEN
%DO;
%PUT &Nte1 Debug is ON in macro &SYSMACRONAME;
%LET lCmnt = ;
%END;
%ELSE
%DO;
%LET lCmnt = *;
%END;
%IF %BQUOTE(&Label) ^= %BQUOTE() %THEN
%DO;
%IF %QUPCASE(&Label) = INTERNAL OR
%QUPCASE(&Label) = EXTERNAL %THEN
%DO;
%&lCmnt.PUT &Nte1 Setting Sensitivity Label to &Label;
%END;
%ELSE
%DO;
%IF &SYSCC < 4 %THEN
%DO;
%LET SYSCC = 4;
%END;
%PUT &Warn1 Invalid value for Sensitivity Label: &Label;
%PUT &Warn2 Defaulting to Internal;
%LET Label = Internal;
%END;
%END;
%ELSE
%DO;
%PUT &Nte1 Sensitivity Label is blank.;
%PUT &Nte2 Defaulting Sensitivity Label to Internal;
%LET Label = Internal;
%END;
%** Set slash to a back slash for Windows or a forward slash for LINUX/UNIX **;
%LET Slash = %SYSFUNC(IFC(%EVAL(&SYSSCP = WIN),\,/));
%LET Path_File = &Path.&Slash&File_Name;
%** If the file does not exist, create the file. **;
%IF %SYSFUNC(FILEEXIST(&Path_File)) %THEN
%DO;
%PUT &Nte1 File &Path_File already exists, so a file was not created.;
%END;
%ELSE
%DO;
%PUT &Nte1 Creating an XLSX skeleton file as follows: &Path_File;
DATA WORK.Skeleton;
Placeholder = ' ';
RUN;
PROC EXPORT DATA=WORK.Skeleton FILE="&Path_File" REPLACE DBMS=xlsx;
SHEET = 'Placeholder';
RUN;
PROC DELETE DATA=WORK.Skeleton;
%END;
%IF NOT %SYSFUNC(LIBREF(XLSX_In)) %THEN
%DO;
FILENAME XLSX_In CLEAR;
%END;
%&lCmnt.PUT &Nte1 Assigning filename &Path_File;
FILENAME XLSX_In ZIP "&Path_File";
%** Read the internal contents of the XLSX file and create a macro array. **;
DATA _NULL_;
LENGTH MemName $200;
FID = DOPEN("XLSX_In");
IF NOT FID THEN
STOP;
MemCount = DNUM(FID);
DO _i = 1 TO MemCount;
MemName = DREAD(FID, _i);
CALL SYMPUTX(CATS("Member", _i), STRIP(MemName), 'G');
END;
CALL SYMPUTX('MemCount', STRIP(PUT(MemCount, 3.)), 'G');
_RC = DCLOSE(FID);
RUN;
%** Iterate through the macro array and extract the internal XML components **;
%DO i = 1 %TO &MemCount;
%&lCmnt.PUT &Nte1 &=i Extracting &&Member&i;
%Extract_Member(&&Member&i, Debug=&Debug);
%END;
%**-------------------------------------------------------------------------**;
%** Relationships **;
%** Read in the .rels relationships file, add a reference to custom.xml, **;
%** and write out a file containing the modified .rels xml **;
%**-------------------------------------------------------------------------**;
%&lCmnt.PUT &Nte1 Creating %SYSFUNC(GETOPTION(WORK))&Slash._rels&Slash..rels;
FILENAME XML_Out "%SYSFUNC(GETOPTION(WORK))&Slash._rels&Slash..rels";
DATA _NULL_;
RETAIN _No_Custom_XML 1;
FILE XML_Out;
INFILE XLSX_In(_rels/.rels) LENGTH=SAS_Length;
INPUT Line $VARYING32767. SAS_Length;
IF INDEX(UPCASE(Line),'CUSTOM.XML') THEN
DO;
&lCmnt.PUTLOG "&Nte1 Custom.xml already exists _rels/.rels";
_No_Custom_XML = 0;
END;
_Position = FIND(Line, '</relationships>', 'i');
IF _Position AND
_No_Custom_XML THEN
DO;
&lCmnt.PUTLOG "&Nte1 Adding custom.xml entry to _rels&Slash..rels";
SUBSTR(Line,_Position) = '<Relationship Id="rId4" Type="http://schemas.openxmlformats.org/officeDocument/2006/relationships/custom-properties" Target="docProps/custom.xml"/></Relationships>';
END;
PUT Line;
RUN;
%**-------------------------------------------------------------------------**;
%** Content Types **;
%** Read in the Content Types XML file, add a reference to custom.xml, and **;
%** write out a file containing the modified Content Types XML. **;
%**-------------------------------------------------------------------------**;
%&lCmnt.PUT &Nte1 Creating %SYSFUNC(GETOPTION(WORK))&Slash.[Content_Types].xml;
FILENAME XML_Out "%SYSFUNC(GETOPTION(WORK))&Slash.[Content_Types].xml";
DATA _NULL_;
RETAIN _No_Custom_XML 1;
FILE XML_Out;
INFILE XLSX_In([Content_Types].xml) LENGTH=SAS_Length;
INPUT Line $VARYING32767. SAS_Length;
IF INDEX(UPCASE(Line),'CUSTOM.XML') THEN
DO;
&lCmnt.PUTLOG "&Nte1 Custom.xml already exists in Content Types.";
_No_Custom_XML = 0;
END;
_Position = FIND(Line, '</Types>', 'i');
IF _Position AND
_No_Custom_XML THEN
DO;
&lCmnt.PUTLOG "&Nte1 Adding custom.xml entry to [Content_Types].xml";
SUBSTR(Line,_Position) = '<Override PartName="/docProps/custom.xml" ContentType="application/vnd.openxmlformats-officedocument.custom-properties+xml"/></Types>';
END;
PUT Line;
RUN;
%**-------------------------------------------------------------------------**;
%** Custom.XML **;
%** Create a file that contains the custom XML that will set the **;
%** label to either External or Internal **;
%**-------------------------------------------------------------------------**;
OPTIONS NOQUOTELENMAX;
%&lCmnt.PUT &Nte1 Creating %SYSFUNC(GETOPTION(WORK))&Slash.docProps&Slash.custom.xml;
FILENAME XML_Out "%SYSFUNC(GETOPTION(WORK))&Slash.docProps&Slash.custom.xml";
DATA _NULL_;
FILE XML_Out;
PUT '<?xml version="1.0" encoding="UTF-8" standalone="yes"?>';
%IF %QUPCASE(&Label) = EXTERNAL %THEN
%DO;
&lCmnt.PUTLOG "&Nte1 Setting Label to External in custom.xml";
PUT '<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>';
%END;
%ELSE
%DO;
&lCmnt.PUTLOG "&Nte1 Setting Label to Internal in custom.xml";
PUT '<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_a8a73c85-e524-44a6-bd58-7df7ef87be8f_Enabled"><vt:lpwstr>true</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="3" name="MSIP_Label_a8a73c85-e524-44a6-bd58-7df7ef87be8f_SetDate"><vt:lpwstr>2022-11-09T06:26:58Z</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="4" name="MSIP_Label_a8a73c85-e524-44a6-bd58-7df7ef87be8f_Method"><vt:lpwstr>Privileged</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="5" name="MSIP_Label_a8a73c85-e524-44a6-bd58-7df7ef87be8f_Name"><vt:lpwstr>Internal Label</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="6" name="MSIP_Label_a8a73c85-e524-44a6-bd58-7df7ef87be8f_SiteId"><vt:lpwstr>db05faca-c82a-4b9d-b9c5-0f64b6755421</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="7" name="MSIP_Label_a8a73c85-e524-44a6-bd58-7df7ef87be8f_ActionId"><vt:lpwstr>70034016-ca32-40c6-a2a6-65d62c0d22d6</vt:lpwstr></property><property fmtid="{D5CDD505-2E9C-101B-9397-08002B2CF9AE}" pid="8" name="MSIP_Label_a8a73c85-e524-44a6-bd58-7df7ef87be8f_ContentBits"><vt:lpwstr>0</vt:lpwstr></property></Properties>';
%END;
RUN;
%LET No_Custom_XML = 1;
%** Open an ODS Package that will be used to create a new XLSX file that will replace the original XLSX file. **;
ODS PACKAGE(New_XLSX) OPEN NOPF;
%DO i = 1 %TO &MemCount;
%IF %SYSFUNC(INDEX(%UPCASE(&&Member&i),CUSTOM.XML)) %THEN
%DO;
%&lCmnt.PUT &Nte1 Custom.xml already exists in the macro array.;
%LET No_Custom_XML = 0;
%END;
%Add_Member(&&Member&i);
%END;
%** Add new XML file for custom properties if it does not already exist. **;
%IF &No_Custom_XML %THEN
%DO;
%&lCmnt.PUT &Nte1 Adding new Custom.xml to XLSX file.;
%Add_Member(docProps/custom.xml);
%END;
%** Create the new XLSX file that will replace the original XLSX file. **;
ODS PACKAGE(New_XLSX) PUBLISH ARCHIVE
PROPERTIES(
ARCHIVE_NAME = "&File_Name"
ARCHIVE_PATH = "&Path"
);
ODS PACKAGE(New_XLSX) CLOSE;
%PUT &Nte2;
%PUT &Nte2 +---------------------------------------------------------------------------------+;
%PUT &Nte1 | Label has been set to &Label for &File_Name;
%PUT &Nte2 | --- End of macro &SYSMACRONAME ---;
%PUT &Nte2 +---------------------------------------------------------------------------------+;
%PUT &Nte2;
%MEND set_sensitivity_label;
/*------------------------------------------------------------------------------*/
/* Macro: extract_member
/* Author: Jim Barbour
/* Date: 08 November 2022
/* Info: This macro extracts the internal XML compoents from an xlsx file.
/* This macro should be called from macro set_sensitivity_level.
/*------------------------------------------------------------------------------*/
/* CHANGE LOG
/*------------------------------------------------------------------------------*/
/* Name: Jim Barbour Date: 08 November 2022
/* Info: Original implementation.
/*------------------------------------------------------------------------------*/
%MACRO extract_member(Member, Debug=NO);
%LOCAL j;
%LOCAL lCmnt;
%IF %QUPCASE(&Debug) = YES %THEN
%DO;
%PUT &Nte1 Debug is ON in macro &SYSMACRONAME;
%LET lCmnt = ;
%END;
%ELSE
%DO;
%LET lCmnt = *;
%END;
%** Parse out the directory portion of each Member. **;
%LET Slash_Count = %SYSFUNC(COUNT(&Member,/));
%LET Last_Slash = %SYSFUNC(FINDC(%SYSFUNC(REVERSE(&Member)),/));
%LET Member_Length = %SYSFUNC(LENGTHN(&Member));
%LET Dir_End = %EVAL(&Member_Length - &Last_Slash);
%LET Save_Dir = ;
%&lCmnt.PUT &Nte1 &=Member &=Member_Length &=Slash_Count &=Last_Slash &=Dir_End;
%** Allocate any needed directories using "dummy" Libnames with the DLCREATEDIR option. **;
%IF &Slash_Count > 0 %THEN
%DO j = 1 %TO &Slash_Count;
%&lCmnt.PUT &Nte1 &=j Allocating any needed directories;
%IF NOT %SYSFUNC(LIBREF(Temp_Lib)) %THEN
%DO;
LIBNAME Temp_Lib CLEAR;
%END;
%LET Mem_Dir = %SYSFUNC(SCAN(&Member,&j,/));
%&lCmnt.PUT &Nte1 &=Mem_Dir resulting from scan of &Member for &=j;
%IF &j = 1 %THEN
%DO;
%&lCmnt.PUT &Nte1 &=j (j should be 1 here);
%LET Lib_Path = %SYSFUNC(GETOPTION(WORK))&Slash%SYSFUNC(TRANWRD(&Mem_Dir,/,&Slash));
%END;
%ELSE
%DO;
%&lCmnt.PUT &Nte1 &=j;
%LET Lib_Path = %SYSFUNC(GETOPTION(WORK))&Save_Dir&Slash%SYSFUNC(TRANWRD(&Mem_Dir,/,&Slash));
%END;
%&lCmnt.PUT &Nte1 &=Mem_Dir &=Lib_Path;
LIBNAME Temp_Lib "&Lib_Path";
%LET Save_Dir = &Save_Dir&Slash&Mem_Dir;
%PUT &Nte1 &=Save_Dir;
%END;
%ELSE
%DO;
%LET Mem_Dir = ;
%END;
%&lCmnt.PUT &Nte1 All directories allocated for &=Member;
%** Allocate the output XML file. **;
FILENAME XML_Out "%SYSFUNC(GETOPTION(WORK))&Slash%SYSFUNC(TRANWRD(&Member,/,&Slash))" ;
%&lCmnt.PUT &Nte1 Extracting %SYSFUNC(GETOPTION(WORK))&Slash%SYSFUNC(TRANWRD(&Member,/,&Slash));
%** Extract and write out each XML file embedded in the XLSX file. **;
DATA _NULL_;
INFILE XLSX_In(&Member) LRECL = 256 LENGTH = SAS_Length
RECFM = F EOF = EOF UNBUF;
FILE XML_Out LRECL = 256 RECFM = N;
INPUT;
PUT _INFILE_ $VARYING256. SAS_Length;
RETURN;
EOF:
STOP;
RUN;
%MEND extract_member;
/*------------------------------------------------------------------------------*/
/* Macro: add_member
/* Author: Jim Barbour
/* Date: 08 November 2022
/* Info: This macro adds internal XML components to an xlsx file.
/* This macro should be called from macro set_sensitivity_level.
/*------------------------------------------------------------------------------*/
/* CHANGE LOG
/*------------------------------------------------------------------------------*/
/* Name: Jim Barbour Date: 08 November 2022
/* Info: Original implementation.
/*------------------------------------------------------------------------------*/
%MACRO add_member(Member, Debug=NO);
%LOCAL Slash_Count;
%LOCAL Last_Slash;
%LOCAL Dir_End;
%LOCAL Dir_Path;
%LOCAL lCmnt;
%IF %QUPCASE(&Debug) = YES %THEN
%DO;
%PUT &Nte1 Debug is ON in macro &SYSMACRONAME;
%LET lCmnt = ;
%END;
%ELSE
%DO;
%LET lCmnt = *;
%END;
%LET Slash_Count = %SYSFUNC(COUNT(&Member,/));
%LET Member_Length = %SYSFUNC(LENGTHN(&Member));
%IF &Slash_Count > 0 %THEN
%DO;
%LET Last_Slash = %SYSFUNC(FINDC(%SYSFUNC(REVERSE(&Member)),/));
%LET Dir_End = %EVAL(&Member_Length - &Last_Slash);
%LET Dir_Path = %SYSFUNC(SUBSTR(&Member,1,&Dir_End));
%END;
%ELSE
%DO;
%LET Last_Slash = 0;
%LET Dir_End = 0;
%LET Dir_Path = ;
%END;
%&lCmnt.PUT &Nte1 &=Member &=Member_Length &=Last_Slash &=Dir_End &=Dir_Path;
%DO;
ODS PACKAGE(New_XLSX) ADD FILE="%SYSFUNC(GETOPTION(WORK))&Slash&Member" PATH="&Dir_Path";
%END;
%MEND add_member;
Click image to register for webinar
