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;
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;
Does this happen if you manually save the XML as XLSX or just when using DDE?
I found this situation happens for both, manually converted and DDE conversion.
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.
I found an indirect manually conversion works.
I firstly saved in .xls file, comments are retained. Then, save the .xls to .xlsx.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.