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

I noticed that after I run the following program to convert .xml to .xlsx file, the existing comments in some fields in .xml are missing in the converted .xlsx doc.

 

I wanted to know if anything I missed or I should add to carry over the comments to .xlsx. Many thanks!

 

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xml"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xlsx"'",51)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;
1 ACCEPTED SOLUTION

Accepted Solutions
mehong19
Obsidian | Level 7

I found solution myself.

 

By a indirect conversion from .xml, .xls. to .xlsx via SAS DDE procedure, I can work it out.

 

Below is the code I modified.

 

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xml"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xls"'",56)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xls"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xlsx"'",51)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

View solution in original post

5 REPLIES 5
Reeza
Super User

Does this happen if you manually save the XML as XLSX or just when using DDE?

mehong19
Obsidian | Level 7

I found this situation happens for both, manually converted and DDE conversion.

ballardw
Super User

If you mean some cells have longish free form text the column might just be inheriting a width from something above where the width is just to narrow to display or the justification ends up too high or low to display in the default. Have you clicked in any of those cells in a spreadsheet program to see if text appears in the cell content window?

 

If no joy with that can you post the cell description text description from the XML of one or more cells with  with this problem using a plain text editor to copy and paste into a forum code box using the {I} menu icon? I suggest the plain text and code box as the main window might want to interpret content pasted into the main window differently.

And example might look a bit like this:

<Row ss:AutoFitHeight="1" ss:Height="15">
<Cell ss:StyleID="rowheader" ss:Index="2"><Data ss:Type="String">% Enroll</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="3"><Data ss:Type="Number">18.8</Data></Cell>
<Cell ss:StyleID="data__r" ss:Index="4"><Data ss:Type="Number">18.8</Data></Cell>
</Row>

 

The key bits are likely between the <Data> and </Data> tags. In the above the first cell of the row would contain "string" data with the value of "% Enroll" and the second cell would have "number" data with the value 18.8. But we are looking for cells that data does not appear in the xlsx. 

 

If the cell doesn't contain "data" then it likely is being ignored by XLSX conversion. If the type is something other than "String" or "Number" that could also be a problem for the conversion.

mehong19
Obsidian | Level 7

I found an indirect manually conversion works.

 

I firstly saved in .xls file, comments are retained. Then, save the .xls to .xlsx.

 

mehong19
Obsidian | Level 7

I found solution myself.

 

By a indirect conversion from .xml, .xls. to .xlsx via SAS DDE procedure, I can work it out.

 

Below is the code I modified.

 

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xml"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xls"'",56)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

options noxwait noxsync;
	x "&ms_excel";
	data _null_;
		x=sleep(3);
	run;

	filename ddecmd dde 'EXCEL|system';

	data _null_;
		file ddecmd;
		put '[ERROR("false")]';
		put '[FILE-OPEN("'"&Out_Path\&Rpt_Name..xls"'")]';
		put '[ERROR("false")]';
		put '[SAVE.AS("'"&Out_Path\&Rpt_Name..xlsx"'",51)]';
		put '[ERROR("false")]';
		put '[QUIT()]';
	run;

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!

How to Concatenate Values

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 webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1200 views
  • 2 likes
  • 3 in conversation