I need some help with streaming CSV output via a stored process. Users can select for every report the output they want to have. We already have: html, pdf, excel and Word. This all works perfectly with a stored process using proc tabulate.
Now we want to add a CSV output option. I've got this working, except in the output I see this when I open it in Notepad:
(remarks: delimiter is semicolon, decimal seperator is a comma)
In proc tabulate prodcode 6 and leisure are values of the row dimension in proc tabulate. The second, third and fourth row in notepad are column dimensions. Sales dep A is 'box'. What should happen is that the data values should begin at the third column in Excel, but they begin the second column. This is not right.
When I choose the Excel output from the stored proces (using ExcelXP tagset) it looks fine. The proc tabulate box on rows 1, 2, 3 and 4 (Excel row) is one cell that spans columns A and B. In the CSV this is not happening. Now I understand that this is formatting that is not available in CSV. So that route can't be taken, right?
Now instead I would like in the CSV output to have an extra cell (delimiter) before the column dimensions (when I view it in a Proc tabulate perspective). That would solve my CSV output problem in presenting the data under the right column dimensions. Can I let proc tabulate do this trick? Or some other solution?
The issue is that CSV does not have a mechanism for spanning headers the way you want. The "workaround" is to use ODS HTML to generate a file in which Excel will "accept" the spanning correctly.
If you use style=minimal for your stored process, the look will approximate what you get from CSV. Or, you other alternative is to use the SASREPORT format for the returned results, in which case, you will not get any style coming back from the stored process (unless you turn the style on in the client application).
This test program will show you what the minimal style looks like. The program uses SASHELP.PRDSALE to do a table similar to what your tabulate program was doing. Note that the ODS CSV syntax is creating a CSV file (you can look at it with Notepad) and both the ODS HTML and ODS MSOFFICE2K syntax are creating HTML files (you can double check this with Notepad). The naming of the MSOFFICE2K file as .XLS is just a convenience to be able to launch Excel automatically when you double click on the file name--this file is really an HTML file that conforms to MS-HTML specs.
VAR actual / STYLE=[font_size=1];
CLASS prodtype / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS product / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS division / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS country / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS region / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS quarter / MISSING ORDER=DATA STYLE=[font_size=1];