how to make a single row Excel output

[Ying]

Thank you for helping on how to create an auto report and keep blanks.

I  run into another problem - the Eexcel output takes two rows for each data after "STYLE={ PRETEXT='  '  ASIS=ON}" is added.  Why?

data t;
order_c='001.0'; overall='64,491      ';  title = '   Number of Customer     '; output;
order_c='002.0'; overall='100%       ';  title = '   Percentage of Customer '; output;
order_c='002.1'; overall='            ';  title = 'Customer Info             '; output;
order_c='002.2'; overall='            ';  title = '   Dollar Spend           '; output;
order_c='003.0'; overall='$728       ';  title = '     Average              '; output;
order_c='004.0'; overall='15%       ';  title = '     0:Zero               '; output;
order_c='005.0'; overall='9%       ';  title = '     1:<600               '; output;
run;

proc report data=t NOWD  ;
    column  title Overall order_c;
DEFINE  title /DISPLAY STYLE={CELLWIDTH=5in}  ; 
DEFINE order_c / /*NOPRINT*/ ORDER;
DEFINE overall /DISPLAY STYLE={CELLWIDTH=1.5in  TEXTALIGN=R} STYLE(HEADER) = {font_weight=bold JUST=C}; 
 
COMPUTE order_c;
   IF substr(order_c,5,1) in ('0','2') THEN
     CALL DEFINE('_c1_', "STYLE","STYLE={ PRETEXT='  '  ASIS=ON}");
   ELSE IF substr(order_c,5,1) = '1' THEN
     CALL DEFINE('_c1_', "STYLE","STYLE={font_weight=bold}");
ENDCOMP;
  run;

[Cynthia_sas · Accepted solution]

Hi:

  Using ASIS=ON has a known issue with making your output appear "double-spaced" or adding to the cell height, as described here: 49853 - Specifying the ASIS=ON attribute with the HTML destination might generate an increased cell .... You did not say how you were getting your report into Excel (what ODS destination, HTML, CSV, MSOFFICE2K or TAGSETS.EXCELXP) you were using. But depending on the destination you choose, the indention that you are attempting might not work when the file is opened in Excel.

  I actually don't understand what you mean by "create an autoreport and keep blanks". What is an "auto-report" in Excel? Is this a report that is linked to a pivot table or to another worksheet or workbook? How do you know that you can create an "auto-report" using SAS? Have you investigated whether this is a proprietary Excel feature or not? For example, when you use ODS HTML, ODS CSV, ODS MSOFFICE2K or ODS TAGSETS.EXCELXP, you are NOT creating binary Excel files. You are creating output that Excel knows how to open and render, but none of these formats have all the "bells and whistles" that are possible inside the Excel application.

  Your "pretext" for some destinations might put a leading space in front of the TITLE string, but the space would be in the same cell as the title. The use of pretext and ASIS=ON might work for RTF and PDF, but you'd have to check with Tech Support about whether it will work in your destination (since you didn't show your destination in the above code). For example, in the output below, you can see that your technique worked for the RTF file, but not for the HTML file.

cynthia

[Ying]

Thank you, Cynthia!

This is a great place to get ideas and helps!!