BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
AlexeyS
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Cynthia_sas
SAS Super FREQ

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:

Cynthia_sas_0-1664661570443.png

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.

View solution in original post

3 REPLIES 3
Cynthia_sas
SAS Super FREQ

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
Pyrite | Level 9
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
SAS Super FREQ

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:

Cynthia_sas_0-1664661570443.png

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 590 views
  • 1 like
  • 2 in conversation