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>
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.
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;
@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.
@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);
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 webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1888 views
-
9 likes
-
-
