Writing a designed spreadsheet and then reading it

[Taliah]

Hello, I need to write a designed spreadsheets (with several sheets, different colrs, formats, etc.) I am doing it with proc report, writing the file as XML. That works fine.

The end users need to view the file, sometimes change manually things in it, resave it, and then my SAS code needs to read that file again. I am using proc import to read the file, but cannot read the XML file with it. I tried different methods and filetypes but cannot make this work. Suggestions for solving this or for other ways/filetypes that would acheive the requirement (writing the designed spreadsheets and then reading it) will be helpfull. (The end users don't always view the file, so requiring them to open the XML file and resave it as xlsx everytime is not an option). Thank you.

[ballardw]

XML expects a sensible layout for data, generally simple columns and rows. Proc report output often does not meet that description, especially when a report spans multiple pages (sheets).

Manual edits themselves can be problematic. A manual entry might look correct but because Excel has no concept of data type for a column will allow values that are not the same as the ones above and below. Also some manual entries can result in something quite a bit different than intended. So after edits the integrity of the XML definition might be "broken".

One approach is to generate two documents, one XML the other XLSX. That is done by just adding another ODS destination tag around the required report.

If your current code looks like

ods tagsets.excelxp file="<path>/file.xml" <other options go here>;

<proc report code goes here>

ods tagsets.excelxp close;

Then this creates two different document files at once.

ods excel file="<path>/file.xlsx" <excel options>;
ods tagsets.excelxp file="<path>/file.xml" <other options go here>;

<proc report code goes here>

ods tagsets.excelxp close;
ods excel close;

[Tom]

How are you writing the XML?  Are you using an ODS tagset?  Are you using the XML (or XMLV2) libref engine?  Did you write it with your own PUT statements in a data step?

How are you trying to read the XML?

[xxformat_com]

Hi,

I would suggest to use ods excel rather than ods tagsets.excelxp.

When you import the excel file, you will have to be really careful in the tab name which varies depending on the way the Excel file is created (proc export, ods excel or manually) and how it is imported (proc import, excel engine, xlsx engine...). There are $ differences in the name.

Feel free to send a short reproducable example  of your code and log to get further help.

[Taliah]

Thank you all for all your answers! Following them I still can't see a solution. Here is the code I am using to write the designed XML and then to read the xlsx file (I have many more sheets, veraibles and compute statements. I placed here an example). What I am currently doing (in line with one of the ansewers above) is having the code write both XML and xlsx files. the xml has the required design and that is the file the users views. If the user makes any manual changes the user must save the changed file as xlsx, and then the code reads either the original xlsx written by the code earlier, or the one saved by the user (regarding one of the answers, in this bussiness setting we must allow the user to make manual changes in the file).

Regarding the last reply - I am trying to use ODS excel you suggested, to write the required designed file, but right now the output using ODS Excel does not look good. 

ods tagsets.ExcelXP file="/dir1/subdir2/TEST.xml"  style=sansprinter;

ods tagsets.ExcelXP options(embedded_titles='yes' embedded_footnotes='yes' sheet_name="ONE" sheet_interval='table'

row_heights = '20,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15,15');

proc report data=TEST1 nowd split="*" spanrows

    style (header)={just=c background=lightorange foreground=black font_weight=bold}

    style (report)={bordercolor=black borderbottomwidth=3pt borderleftwidth=3pt

                     borderrightwidth=3pt};

    column A B;

    define A / display "A" missing format=percentn8.3

           style=[just=c cellwidth=0.8in];

    define B / display "B" missing format=percentn8.3

           style=[just=c cellwidth=0.8in];

compute b;

    if compress(b) > 0 then

            call define (_COL_, 'style', 'style=[foreground=green font_weight=bold]');

    if compress(b) < 0 then

            call define (_COL_, 'style', 'style=[foreground=red font_weight=bold]');

endcomp;

run;

ods tagsets.ExcelXP close;

proc import

     datafile="/dir1/subdir2/TEST2"

     DBMS=xlsx out=A replace;

     getnames=YES; SHEET='A'; run;

And similar proc import statements for each additional sheet.

Thank you for your help.

[Tom]

If you care so much about what it LOOKS like then it sounds like it is intended as a REPORT and not a source of DATA.

If you need to transfer data use some other method.

[Tom]

The main thing that you need to do if you want to read back in the spreadsheet that the XML file you created defines is to have whoever is making the changes to it save the new version as an actual XLSX file.  Then you could read it with SAS.  In fact a CSV file might be better because then you can read it and have complete control over how the variables are defined.

[Taliah]

Thank you. What you suggested regarding having the user save the XML as XLSX is what we are currently doing. I was trying to find a better solution. The csv option you suggested - is there a way to create a disgen CSV file - contitional colors, manage the style including bold type,  foramts, thick gridlines were needed (all possible with XML in proc report)? Thank you.

[SASKiwi]

I note you are using the ODS ExcelXP tagset to create your spreadsheets. What SAS 9.4 maintenance release do you use? ODS Excel only became more reliable with recent maintenance releases.