Help using Base SAS procedures

PROC REPORT option to show all Group-By data

Not applicable
Posts: 0

PROC REPORT option to show all Group-By data

In a proc report, how do you get "group" columns to repeat their value in every row, rather than the default that lists the group value once and all subsequent rows are blank until the next new value appears? I think I've done this once before, but can't find the option. For example, if I group by Country and State, the result on the first line would show the Country as "USA" along with the first state. The second line would show a blank for Country, and would show the next State. If I want country to be repeated all the way down, how do I do it? Thanks!
Posts: 9,365

Re: PROC REPORT option to show all Group-By data

Posted in reply to deleted_user
If you change the usage option from GROUP to DISPLAY, you will get the value of Country to appear on every line. However, this would then have the side effect of turning "off" the automatic grouping/ordering that comes with a usage of GROUP.

There isn't an option that gives you GROUPing and DISPLAYing on every row. However, PROC REPORT does have a COMPUTE block and you can easily make a "display" version of Country. The code below creates an example, using REGION from SASHELP.SHOES.

If this works the way you want, then the only thing you have to do is use NOPRINT on the DEFINE statement for REGION as a GROUP variable.
[pre]define region / group noprint;[/pre]

This would allow REGION to do the grouping, but what you'd see would be the DISP_REG variable on the report itself.

proc report nowd;
where region = 'Asia';
column region disp_reg product sales;
define region / group;
define disp_reg/computed;
define product / group;
define sales / sum;
compute before region;
length holdreg $25;
holdreg = region;
compute disp_reg / character length=25;
disp_reg = holdreg;
Not applicable
Posts: 0

Re: PROC REPORT option to show all Group-By data

Posted in reply to Cynthia_sas
Ok, thanks. I think I've "tricked" it before similar to what you've done. If I recall correctly, I listed a second variable in the column statement based off the original variable, such as disp_reg=region (I might have that backwards), then I grouped by the original variable with a "noprint" option. Whatever the case, either method seems like a lot of work to simply correct a little formatting issue of displaying a value. Maybe this could be a new option in an upcoming version. The option could be in the Define statement.

The reason I need this is that there are some reports where there aren't many "blanks" in the grouped variable. I might be grouping on a few different variables, and most of the groupings result in one completely populated line. Occasionally a second value appears for the same grouped variable, resulting in a "blank" for the preceding grouped variable, and to the casual observer it appears like my report is missing a value. Users of the report don't always recall that the blank really means it's the same as the value above. Another reason why it can be handy to have the report display all the "group" variables is for when users want to copy the report (generally an html page) and paste into excel. To work with data like that, you want all the fields populated. I can't simply use "display", though, because I want the functionality of rolling up the data by the grouped variable. You get the idea. Thanks.
Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation