CSV output problem

[deleted_user]

Hello,

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)

"Sales dep A";;"Week";;;"Total"
; "200725"; "200726"; "200727";;
; "Days"; "Days"; "Days";;
; "6"; "6"; "6";;
"prodcode 6"; "leisure"; "6000"; "5500"; "7000"; "18500"

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?

This is the proc tabulate:

PROC TABULATE DATA=resultaat STYLE={font_size=1}
&v_format;

VAR &p_uren / STYLE=[font_size=1];
CLASS &p_tijdvak / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS rubriek / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS omschrijving_rubriek / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS werkdagen / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS eenheid_omschrijving / MISSING ORDER=DATA STYLE=[font_size=1];
CLASS letter_rubriek / MISSING ORDER=DATA STYLE=[font_size=1];

CLASSLEV &p_tijdvak / STYLE=[font_size=1] ;
CLASSLEV rubriek / STYLE=[font_size=1] ;
CLASSLEV omschrijving_rubriek / STYLE={NOBREAKSPACE=ON} STYLE=[font_size=1];
CLASSLEV werkdagen / STYLE=[font_size=1] ;
CLASSLEV eenheid_omschrijving / STYLE=[font_size=1] ;

TABLE
/* Page Dimension */
&variabel1
letter_rubriek={LABEL=''},
/* Row Dimension */
rubriek={LABEL=''}*
omschrijving_rubriek={LABEL='' },
/* Column Dimension */
&variabel2
werkdagen={LABEL='Days'}*
&p_uren ={LABEL=''}*sum=''
all={LABEL='Total' STYLE={JUST=RIGHT VJUST=BOTTOM}}
*&p_uren={LABEL=''}
*sum=''
/* Table Options */
/ BOX={LABEL=_page_ }

NOCONTINUED;
RUN;

Hope anyone can help.

Cheers,

Gahan

[Cynthia_sas]

Hi:
I believe you are running into this issue:
http://support.sas.com/techsup/unotes/SN/007/007926.html

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.

cynthia
[pre]
ods listing;
ods csv file='c:\temp\tab_forum.csv' ;
ods html file='c:\temp\tab_forum.html' style=minimal;
ods msoffice2k file='c:\temp\tab_forum_mso.xls' style=minimal;
PROC TABULATE DATA=sashelp.prdsale STYLE={font_size=1}
f=comma14.;

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];

CLASSLEV product / STYLE=[font_size=1] ;
CLASSLEV division / STYLE=[font_size=1] ;
CLASSLEV country / STYLE={NOBREAKSPACE=ON} STYLE=[font_size=1];
CLASSLEV region / STYLE=[font_size=1] ;
CLASSLEV quarter / STYLE=[font_size=1] ;

TABLE
/* Page Dimension */

division={LABEL=' '},
/* Row Dimension */
country={LABEL=''}*
quarter={LABEL='' },

/* Column Dimension */
actual
region={LABEL='Region'}*
actual ={LABEL=''}*sum=''
all={LABEL='Total' STYLE={JUST=RIGHT VJUST=BOTTOM}}
*actual={LABEL=''}
*sum=''
/* Table Options */
/ BOX={LABEL=_page_ } style={borderwidth=1 font_weight=medium}

NOCONTINUED;
RUN;
ods _all_ close;

[/pre]