topic Troubleshooting proc report in SAS Procedures

Troubleshooting proc report

sarahsasuser — Mon, 08 Jul 2013 14:49:04 GMT

Dear All,

I would like to make a report table that looks like the table below. Since all the data is in the dataset, I don't actually want to calculate any fields, I just want the data to display. The log says that Region is a analysis variable, even though it is formatted as a character variable in the dataset. It will not allow me to use display for all of the variables.

Further, I need to run this report by building id and name. I'd like both of those variables to show up in the title, but am unable to create a macro for both id and name.

Any help is appreciated. Thanks.

Building #: 1
2222 Smith St

Type	Subtype	Region	Count	Expected	Timeliness	# ontime	#late	% ontime	% late
Residential	Park	23a	3	4	3	3	1	75%	25%

Grand Total			3	4	3	3	1	75%	25%

proc report data=have nowd split='/';

title 'Building_id and Building_name';

by building_id;

columns Type Subtype Region Count Expected count_ontime late pct_within pct_not_within;

define Type/display " Type";

define subtype /display "subtype";

define Region/display "Region";

define Count/ display "Count";

define Expected/ display 'Expected';

define count_ontime/display '# Ontime';

define late/display '# late';

define Pct_within/display '% ontime' format=PERCENT7.0;

define Pct_not_within/display '% late' format=PERCENT7.0;

rbreak after / summarize suppress style={background=light gray font_weight=bold};

compute Region;

if _break_ ne ' ' then

call define('Region','style','style=[pretext="Grand Total" font_weight=bold]');

endcomp; */

run;

Re: Troubleshooting proc report

Ksharp — Mon, 08 Jul 2013 15:32:21 GMT

compute after;

line ' ' ;

endcomp;

Re: Troubleshooting proc report

sarahsasuser — Mon, 08 Jul 2013 15:38:14 GMT

Hi Ksharp,

This gives me a blank row.

What if I modified the code to take out the rbreak and the compute lines? When I do that SAS automatically sums the statistics for me. However, how do I format that last line so that it is gray and bold? Is it also possible to add "Grand Total" to the row?

Thanks,

Sarah

Re: Troubleshooting proc report

Ksharp — Mon, 08 Jul 2013 16:05:58 GMT

Sorry. I have to leave now. Cynthia@sas will give you answer if you post it at

ODS and Base Reporting

Re: Troubleshooting proc report

Cynthia_sas — Mon, 08 Jul 2013 23:10:06 GMT

Hi:

I have posted part of a response in the ODS forum (https://communities.sas.com/message/173513#173513) but I did not see your full question here. So there is still some unanswered.

You wanted to know how to get BY variable information in the TITLE. That is easily done like this:

options nobyline;

ods html ...;

proc report data=mydata nowd;

by xxx yyy;

title 'Title1 for #byval(xxx)';

title 'Regarding #byval(yyy)';

... more code...

run;

ods html close;

options byline;

title;

The "special" references #BYLINE, #BYVAL and #BYVAR can by used in a SAS TITLE statement when you have turned on BY group processing for your procedure. A complete example is here (Base SAS(R) 9.2 Procedures Guide) and even though it uses PROC PRINT, it works the same way with other SAS procedures that allow BY group processing and also produce report output. Look at #2 on this site:SAS(R) 9.3 Statements: Reference

You also said that: "The log says that Region is a analysis variable, even though it is formatted as a character variable in the dataset. It will not allow me to use display for all of the variables." The default PROC REPORT usage for character variables (internally stored as character) is DISPLAY; but the default usage for numeric variables (not format, but internal storage) is ANALYSIS. So given your statement, I assume that Region might be numeric, even though formatted to show a text string. You can change what you see on the report break for a numeric variable, you just have to do some finagling...see the code below. In this code, I use AGE on the report as a NOPRINT item, just to have the numeric version on the variable on the report. Then I make a character string out of the AGE variable, using the PUT function. One thing that is ALWAYS true of using the RBREAK statement is that it causes an internal variable called _BREAK_ to have the value of _RBREAK_ -- so you always know when you are on the summary line on a report. I like that better than using the test for not= ' ' for _break_, because depending on your other statements, the value of _BREAK_ could be something other than space. The PRETEXT trick for PROC REPORT does not always work reliably for some variables (as you discovered), but my method of making a COMPUTED item will always work.

Also, you said that the data was in the file and you just wanted to highlight it. I'm not sure what you mean by this. But in the code below, you can see how I used CALL DEFINE in a COMPUTE block to highlight only certain rows based on the values of the SEX and NAME variable for each report row.

cynthia

ods listing close;

ods html file='c:\temp\showage.html';

proc report data=sashelp.class nowd

style(summary)={background=cxcccccc font_weight=bold};

where age le 13;

column age agedisp sex name height weight;

define age / display noprint /* numeric var */;

define agedisp / 'Age' computed style(column)={just=r};

define sex / display ;

define height / sum analysis;

define weight / sum analysis;

rbreak after /summarize;

compute weight;

if sex = 'F' then do;

if name in ('Alice', 'Louise') then

call define(_row_,'style','style={background=pink}');

else if substr(name,1,1) = 'J' then

call define(_row_,'style','style={background=yellow}');

end;

endcomp;

compute agedisp / character length=15;

** character version of numeric var;

agedisp = put(age,2.0);

if _break_ = '_RBREAK_' then do;

agedisp = 'Grand Total';

end;

endcomp;

run;

ods html close;