proc report : cancel style

[AlexeyS]

Hi All,

I have two questions about proc report with ods excel

1. When i run simple proc report using ods excel i get some default style, like "blue style", but i want some basic style - without style 🙂 How can i do it?

2. Does proc report enable us in on define statement write multiple variables. I have macro variable that contains all variables in the dataset  i want to export, but i want to do format to only numeric variables in the define statement. What can i do?

Thank you,

Alexey

[Cynthia_sas]

Hi:
Regarding your questions:
1) The default style for ODS EXCEL is typically the HTMLBLUE style. If you want no style, then in the ODS EXCEL statement, after the FILE= option, you would specify STYLE=MINIMAL -- that gives you basically black and white font with no colors.
2) PROC REPORT would support a FORMAT statement like this:
format _numeric_ best12.2 _character_ $25.;
without needing macro variables or something like this with either 1 macro variable or multiple macro variables:
format &list1 dollar14. &list2 comma6. &list3 $25.;
Otherwise, you can list multiple variables on a DEFINE statement but for your stated purpose of changing the formats for a group of variables, then the FORMAT statement method would serve that purpose better.
Cynthia

[AlexeyS]

Thank you for your answer. I did what you said and it worked excellent.
But the result i got in excel is not result in dataset in proc report. Dataset contains more than 100 rows, and in excel i get only one row and i don't understand the values i saw. What i did wrong with proc report? And how can i add an option bold of header?

I write the following syntax :

ods excel file "..." style=minimal options(sheet_name="best");
proc report data=test;
column &varlist.;
format _numeric_ 12.2;
run;
ods close;

Thank you

[Cynthia_sas · Accepted solution]

Hi:

  If you have all numeric variables listed on the COLUMN statement, then the default behavior of PROC REPORT is to treat them as ANALYSIS usage with a default statistic of SUM, which means that all of your numeric columns in the rows will be summarized down to 1 row unless you change the usage of the numeric columns to DISPLAY or list a character variable or a DISPLAY item on the COLUMN statement before the numeric variables. Like this:

SASHELP.CLASS has 19 rows. As you can see in Report #1, all 19 rows are summarized down to 1 row. But in Report #2, with a usage of DISPLAY for the numeric variables, all 19 rows are displayed. In a similar way, with Report #3, listing a character variable first on the COLUMN statement and defining the usage as DISPLAY also causes all 19 rows to be displayed.

  Learning about PROC TABULATE and PROC REPORT and how to change the usages in the DEFINE statement for PROC REPORT is part of our Report Writing class: https://support.sas.com/edu/schedules.html?crs=RPT1&ctry=US .

  Hope this explains why your report did what it did.

Cynthia

  Also just a few comments about your code -- which could be caused by typing and not copying/pasting the working code:

1) the ODS EXCEL statement should have an = sign after FILE:

ods excel file='c:\temp\myreport.xlsx' style=minimal options(sheet_name='myname');

... ... code ... ...

2) then the ending would be 

ods excel close;

NOT just the statement you show (ods close;), which would be incorrect.