One advantage of having a macro to make changes to a Zip archive is that one can apply bulk changes to a Windows or UNIX/Linux directory.  One would iterate through the directory, set the arguments, and call the macro for each Zip file in the directory.   If one needed to apply a mass change to all Excel (or other Zip archives) in a directory, this could be quite handy.   Jim ... View more

If it were of interest, I've added code (bundled up into a macro) for my work around for the CRC-32 check sum errors I encountered when using the Zip engine in the original thread:  https://communities.sas.com/t5/ODS-and-Base-Reporting/Excel-with-Custom-Property-Names/td-p/842420/page/2   Jim ... View more

@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;     ... View more

Hi, Chris,   Thanks.  I'll have to see if I can replicate the problem.  It does however seem to be a Zip engine issue inasmuch as I'm just making text based edits to a zip archive.  WinZip, which presumably knows nothing of Microsoft Excel's inner mysteries, handles things just fine.   Right now, I've got to get a Production version of this working.  Hopefully I can experiment more later this week.  It's a very odd problem that the files that I'm not changing are the ones that are getting corrupted.  I suspect it's the inner mysteries of multi-directory Zip archives more than it is the inner mysteries of Excel, but we'll see.   Thanks for taking the time to respond,   Jim ... View more

I was able to create a work-around for the SAS Zip engine CRC-32 check sum problems.  See https://communities.sas.com/t5/SAS-Programming/SAS-Zip-Engine-Problem-CRC32-Checksum-Error/m-p/842913#M333289   Things are now working.  I'll post code once I get things cleaned up and organized.   Jim ... View more

@Tom, @Kurt_Bremser, and @ballardw,   I was able to create a work-around that a) created the custom Excel properties I originally sought and b) doesn't have the CRC-32 check sum errors that using the SAS Zip engine causes.  In the event this is of interest, read on.  I'll eventually post SAS code for those who might be interested -- once I clean things up a bit and package it into a macro.   @Tom wrote: If that doesn't fix it then what if you make a whole new archive and copy all of the files there, picking the proper version of each file? Yeah, that's what I've kind of resigned myself to: Extract everything from an xlsx file  Update all files in unzipped form Delete the original xlsx file Create a new xlsx file with the same name as the original xlsx file. I was able work-around the Zip engine invalid CRC-32 problem by doing steps 1, 2, and 4 of the steps listed above.  Rather than delete the original xlsx file, I just create a new xlsx file using ODS Package.  I presume that ODS Package overlays the original xlsx file.  By using ODS Package, I can now open the resultant xlsx file with Excel, the contents display correctly, and the Internal/External label custom property is set.    I'm going to leave this topic unsolved for a bit longer in case @ChrisHemedinger has a solution for the Zip engine problem so others don't have to resort to my somewhat kludgey work-around.  It's a bit of coding to get the work-around in place.    Once I clean up my spaghetti and turn it into a macro, I'll post the SAS code in the original thread, https://communities.sas.com/t5/ODS-and-Base-Reporting/Excel-with-Custom-Property-Names/m-p/842420#M26004    Thanks for all the help and ideas,   Jim   ... View more

Further research: Although all xml files in the xlsx file have some kind of update, really only three must be updated/added to set the label: custom.xml (add) [Content_Types].xml (update) _rels/.rels (update) Adding custom.xml works just fine, but updating either [Content_Types].xml or _rels/.rels causes other xml files to become corrupted, typically in the xl folder.  Interestingly, when I update either [Content_Types].xml or _rels/.rels, the updates themselves work fine.  It is other xml files that get corrupted. I tried deleting [Content_Types].xml (or _rels/.rels) before updating, but if I delete them before updating, the update Data step can't open the xlsx file.  SAS says "The filename I:\commercial\user\jbarbou3\ODS_Excel\Class_17.xlsx is not a zip file". Pretty stuck here.  I wonder if @ChrisHemedinger might be able to help a poor soul out.   Synopsis:  I'm trying to update/add the three above listed xml files in an xlsx file.  The add works fine, but either or both of the two updates causes corruption in other xml files inside the xlsx, files that I haven't touched.  Any tips here?   Jim ... View more

@Tom wrote: What if you copy the new files into the archive instead? That does work when I copy them in by hand.  I haven't tried that using a SAS program.  I'm trying to avoid any OS level commands since my program needs to work in both Windows and UNIX type environments.   @Tom wrote: If that doesn't fix it then what if you DELETE the old file first? When I've tried deleting the old file first, then SAS gives me a message that "file is not a zip file," and I can't proceed.  I could play with this some more though.   @Tom wrote: If that doesn't fix it then what if you make a whole new archive and copy all of the files there, picking the proper version of each file? Yeah, that's what I've kind of resigned myself to.  Extract everything from an xlsx file  Update all files in unzipped form Delete the original xlsx file Create a new xlsx file with the same name as the original xlsx file. As I dig a little further, I see that subtle changes are made to every xml file inside an xlsx Zip archive when this Internal/External label is set.  When Proc Export with just default settings creates an xlsx file, 10 xml files are created inside.  I want to add a custom.xml file, for a total of 11 files.  Hard coding all of these xml changes/adds seems like it may not be practical, particularly if the Excel VB code ever changes.  I'm going to have to talk to my internal client and see if they can just set the label manually.  Given the number of xml files and SAS' seeming difficulty with CRC32 check sums, my approach may not really be workable.   I've learned one interesting thing here:  The use of a MemVar which does essentially for Zip files what a FileVar would do for unzipped files.  One should however beware of potential CRC32 check sum errors.   Jim               ... View more

@Tom wrote: 7Zip seems to give a clue as to what is happening when I attempt to extract the contents of the modified file.  You seem to have two copies of the same file in the ZIP file. Interesting.  WinZip doesn't show any such thing.  I tried deleting the xml components prior to updating them, but then SAS gave me a message that "file is not a zip archive."  Let me play with it a bit.   Jim ... View more

@Tom wrote: Can you recreate the issue with a simpler program? Hmm.  Good question.  Maybe.  I need to have a coordinated set of changes to an Excel file.  Of the four updates/adds I'm making, all have to be in place in order for things to work.  Just adding, say, the custom.xml does nothing since the Content Types and Rels xml files don't have any pointers to the file.  In addition, when I've done an update to just one xml file, it works just fine in terms of the CRC32 check sums.  It's something about doing multiple updates to a single Zip archive that is throwing the check sums off (from what I can tell).  But this is a good idea.  Let me play with it a bit.   Jim ... View more

@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 ... View more

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 ... View more

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.       ... View more

@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);       ... View more