BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
titus
Obsidian | Level 7

I have an Excel template saved to our Unix server. The template has 4 tabs or sheets:
- Chart

- Valid

- Invalid

- Summary

When I export to Excel I'm overwriting the Valid, Invalid, and Summary tabs  with new data so that only the Chart tab remains from the original.


After export, I open the file and any numeric fields on the newly exported tabs seem to inherit the formatting from my Chart tab.

 

CHART tab:

mychart.PNG

SUMMARY tab:

Summary.PNG

The format of the columns in SAS EG:

SAS.png

And finally the code to export the files:

	%macro XLMultiTab(	sn= /*[Required] Sheet Name*/,
						fn= /*[Required] File Name*/,
						dn= /*[Required] Fully Qualified DataSet Name*/,
						fp=/users/apps/data/ /*[Optional] Default value is the docman_wfo directory*/);

	proc export
		data=&dn.
		outfile= "&fp.&fn..xlsx"
		dbms=xlsx replace;
		sheet="&sn.";
	run;

	%mend;

	/*CREATE EXCEL FILE*/
	%XLMultiTab(sn=Summary,	fn=ReindexReconciliation,	dn=Summary);
	%XLMultiTab(sn=Valid,	fn=ReindexReconciliation,	dn=VALID);
	%XLMultiTab(sn=Invalid,	fn=ReindexReconciliation,	dn=Invalid_DocIDs);

 

 

Any thoughts on why this is happening and how to resolve?

1 ACCEPTED SOLUTION

Accepted Solutions
titus
Obsidian | Level 7

I played around with the files this morning and was unable to determine exactly which cell it was sourcing the numeric formatting from off of my Chart tab. I would determine it was one specific cell and would clear the formatting of that cell and then the next run the formatting would be back and sourcing from a different cell. It seemed to be choosing the cells at random. I even placed a dummy tab at the front of the workbook hoping that it would source the formatting from the dummy tab but it was still using the Chart tab formatting.

 

In my research, I discovered that an xlsx file is essentially just a ZIP file. So I changed my XLSX to a ZIP and then looked at the XMLs within. I'm not great with XML but I believe that what's happening is the XMLs store all the formats found within the workbook into defined formats for quick reference.

 

Capture.PNG

 

I believe that when SAS writes to the XLSX its defaulting the number formatting to one of these packaged formats. Perhaps someone with more XML knowledge would be able to modify the XML so that the desired numeric format is chosen as default, but that's not me.

 

 

Knowing that the format was coming from the XML, I changed my file format from XLSX to XLS and that has solved my formatting issue.

View solution in original post

3 REPLIES 3
rogerjdeangelis
Barite | Level 11

 

HAVE TWO FORMATATED TABS USING A TEMPLATE (SHEETS TABONE AND TABTWO)
(NOT SURE THIS HELPS, BUT I DONT HAVE ALL YOUR CODE)
TABONE (WEIGHT EXCEL FORMAT ##0) Name Sex Age Name Sex Age Weight Alfred M 14.0 113 Alice F 13.0 84 Barbara F 13.0 98 Carol F 14.0 103 Henry M 14.0 103 James M 12.0 83 Jane F 12.0 85 Janet F 15.0 113 Jeffrey M 13.0 84 John M 12.0 100 Joyce F 11.0 51 Judy F 14.0 90 Louise F 12.0 77 Mary F 15.0 112 Philip M 16.0 150 Robert M 12.0 128 Ronald M 15.0 133 Thomas M 11.0 85 William M 15.0 112 TABONE -------------------------------- TABTWO (WEIGHT EXCEL FORMAT ##0.#0 ) Name Sex Age Weight Alfred M 14 112.50 Alice F 13 84.0 Barbara F 13 98.0 Carol F 14 102.50 Henry M 14 102.50 James M 12 83.0 Jane F 12 84.50 Janet F 15 112.50 Jeffrey M 13 84.0 John M 12 99.50 Joyce F 11 50.50 Judy F 14 90.0 Louise F 12 77.0 Mary F 15 112.0 Philip M 16 150.0 Robert M 12 128.0 Ronald M 15 133.0 Thomas M 11 85.0 William M 15 112.0 WANT ( UPDATE WEIGHT BUT KEEP ALL THE FORMATTING) TABONE (SAME EXCEL FORMAT ##0) Name Sex Age Name Sex Age Weight Alfred M 14.0 24 Alice F 13.0 24 Barbara F 13.0 24 Carol F 14.0 24 Henry M 14.0 24 James M 12.0 24 Jane F 12.0 24 Janet F 15.0 24 Jeffrey M 13.0 24 John M 12.0 24 Joyce F 11.0 24 Judy F 14.0 24 Louise F 12.0 24 Mary F 15.0 24 Philip M 16.0 24 Robert M 12.0 24 Ronald M 15.0 24 Thomas M 11.0 24 William M 15.0 24 TABTWO (EXCEL FORMAT ##0.#0 ) Name Sex Age Weight Alfred M 14 99.35 Alice F 13 99.35 Barbara F 13 99.35 Carol F 14 99.35 Henry M 14 99.35 James M 12 99.35 Jane F 12 99.35 Janet F 15 99.35 Jeffrey M 13 99.35 John M 12 99.35 Joyce F 11 99.35 Judy F 14 99.35 Louise F 12 99.35 Mary F 15 99.35 Philip M 16 99.35 Robert M 12 99.35 Ronald M 15 99.35 Thomas M 11 99.35 William M 15 99.35 SOLUTION * CREATE HEAVILY FORMATTED WRKBOOK TO UPDATE WEIGHT) %let excel=d:/xls/utl_twotab.xlsx; %utlfkil(&excel); ods listing close; ods escapechar='^'; ods excel file="&excel"; ods excel options ( start_at = "D3" tab_color = "yellow" autofilter = 'yes' orientation = 'landscape' zoom = "100" suppress_bylines = 'no' embedded_titles = 'yes' embedded_footnotes = 'yes' embed_titles_once = 'yes' gridlines = 'yes' frozen_headers = 'yes' absolute_column_width = "30pct,22pct,22pct,23pct" frozen_rowheaders = 'yes' ); ;run;quit; ods excel options(sheet_name="tabone" sheet_interval="none"); title;footnote; proc report data=sashelp.class nowd missing split='+' style(header)={font_size=13pt just=left font_face=Times} style(column)={font_size=11pt font_face=Times } ; cols ( "Name Sex Age" name sex age weight ); define Name / 'Name' style={just=left}; define Sex / 'Sex' style={just=left}; define Age / 'Age' style={just=right tagattr='format:##0.#0'}; define Weight / 'Weight' style={just=right tagattr='format:##0'}; run;quit; ods excel options ( start_at = "F9" tab_color = "green" autofilter = 'no' orientation = 'landscape' zoom = "100" suppress_bylines = 'no' embedded_titles = 'yes' embedded_footnotes = 'yes' embed_titles_once = 'yes' gridlines = 'no' frozen_headers = 'no' absolute_column_width = "40pct,12pct,22pct,13pct" frozen_rowheaders = 'yes' ); ;run;quit; ods excel options(sheet_name="tabtwo" sheet_interval="none"); title;footnote; proc report data=sashelp.class nowd missing split='+' style(header)={font_size=13pt just=right font_face=Times} style(column)={font_size=11pt font_face=Times } ; cols ( "Name Sex Age" name sex age weight ); define Name / 'Name' style={just=left}; define Sex / 'Sex' style={just=left}; define Age / 'Age' style={just=right tagattr='format:##0'}; define Weight / 'Weight' style={just=right tagattr='format:##0.#0'}; run;quit; ods excel close; libname sd1 "d:/sd1"; options validvarname=upcase; data sd1.weight; set sashelp.class(keep=weight); ;run;quit; * You can run this from IML; %utl_submit_r64(' library(XLConnect); library(haven); weight<-read_sas("d:/sd1/weight.sas7bdat"); weight$WEIGHT<-23.546; wb <- loadWorkbook("d:/xls/utl_twotab.xlsx"); sheets <- getSheets(wb); setStyleAction(wb,XLC$"STYLE_ACTION.NONE"); writeWorksheet ( wb,weight$WEIGHT,sheets[1],startRow=5,startCol=7,header = FALSE); weight$WEIGHT<-99.346; writeWorksheet ( wb,weight$WEIGHT,sheets[2],startRow=11,startCol=9,header = FALSE); wb <- saveWorkbook(wb,"d:/xls/utl_twotab.xlsx"); ');

 

titus
Obsidian | Level 7

Thanks for the response, Roger. However, as I understand the ODS method of creating an Excel file, it will always generate a new file instead of updating the existing file which will wipe out my Chart tab which needs to remain intact.

titus
Obsidian | Level 7

I played around with the files this morning and was unable to determine exactly which cell it was sourcing the numeric formatting from off of my Chart tab. I would determine it was one specific cell and would clear the formatting of that cell and then the next run the formatting would be back and sourcing from a different cell. It seemed to be choosing the cells at random. I even placed a dummy tab at the front of the workbook hoping that it would source the formatting from the dummy tab but it was still using the Chart tab formatting.

 

In my research, I discovered that an xlsx file is essentially just a ZIP file. So I changed my XLSX to a ZIP and then looked at the XMLs within. I'm not great with XML but I believe that what's happening is the XMLs store all the formats found within the workbook into defined formats for quick reference.

 

Capture.PNG

 

I believe that when SAS writes to the XLSX its defaulting the number formatting to one of these packaged formats. Perhaps someone with more XML knowledge would be able to modify the XML so that the desired numeric format is chosen as default, but that's not me.

 

 

Knowing that the format was coming from the XML, I changed my file format from XLSX to XLS and that has solved my formatting issue.

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!

Discussion stats
  • 3 replies
  • 1168 views
  • 0 likes
  • 2 in conversation