Proc Report - Renaming the summary line for break and rbreak lines

[mary_mcneill]

I am trying to rename the Break after Region to say Total instead of Region 11 repeated. I have tried using compute after but since I did that to rename the rbreak, it is not allowing me to use that twice. I have also tried to code within the same compute block, but it is not renaming the Region breaks to Total. Any suggestions?

Proc Report Code

options missing=0;

proc report data=work.syp1113 nowd;

where year=2024;

column region county month Total;

define region / group 'Region' width=15 format=$regionfmt. order=data;

define county / group 'County' width=20 format=$cntyfmt.;

define month / across 'Month'  width=20 format=monthfmt. order=internal;

define Total/computed;

  compute Total;

     Total=sum(_c2_, _c3_ ,_c4_, _c5_,_c6_,_c7_,_c8_,_c9_,_c10_,_c11_,_c12_,_c13_);

  endcomp;

break after Region /summarize style=[font_weight=bold];

rbreak after /summarize style=[font_weight=bold background=lightgrey];

compute after;

  region='State';

endcomp;

run;

Current Output

I would like the second Region 11 to be named Total instead of repeating the Region name.

[Ksharp · Accepted solution]

Better post your dataset "work.syp1113 " to test your code, otherwise we just guess where is the problem.

You could add one more COMPUTE block to rename it.

options missing=0;
proc report data=work.syp1113 nowd;
where year=2024;
column region county month Total;
define region / group 'Region' width=15 format=$regionfmt. order=data;
define county / group 'County' width=20 format=$cntyfmt.;
define month / across 'Month'  width=20 format=monthfmt. order=internal;
define Total/computed;
  compute Total;
     Total=sum(_c2_, _c3_ ,_c4_, _c5_,_c6_,_c7_,_c8_,_c9_,_c10_,_c11_,_c12_,_c13_);
  endcomp;
break after Region /summarize style=[font_weight=bold];
rbreak after /summarize style=[font_weight=bold background=lightgrey];
compute after;
  region='State';
endcomp;
compute after Region; 
region='State'; 
endcomp;
run;

BTW, for such CROSS table, I think the best choice is using PROC TABULATE.

[mary_mcneill]

Thank you so much! I will also explore Proc Tabulate as al alternative as well 🙂

[mary_mcneill]

I updated this using proc tabulate, and I agree the cross table works better for this!

I did have a quick question though. I have some counties within a Region that do not have any data but I still want that county label to be displayed within the Region variable with zeros for all months. When I add printmiss as an option to the table it will display all counties for the state for each Region, not just the counties within the Region itself. Do you know how I can code to only display the counties within the region to display if there is no data for that county?

options missing=0;
proc tabulate data=work.data format=5.;
format region $regionfmt. county $cntyfmt. month monthfmt. ;
class region county month year / preloadfmt;
where year = 2021;
table Region*(county all) all,month all;
keylabel n=' ' all='Total';
run;

Below is my output currently with the above code. Region 1 has 8 counties, but County 1 is missing because there is no data for that year. I am trying to still have the county name display with all 0s for each month.

In my SAS code, Region is coded the following way:

format region $10.;
if county in ('County 1','County 2','County 3','County 4','County 5','County 6','County 7','County 8')
then region = 'Region 1';

[Ksharp]

That would be better if you could post some data,so I could test your code.

For your this special case,you could use option CLASSDATA= :

data have;
do region='A','B','C';
 do county='X','Y','Z';
  do month=1 to 12;
    if region = 'C' and county = 'Z' then leave;
    output;
  end;
 end;
end;
run;

data classlevel;
 do region='A','B','C';
 do county='X','Y','Z';
 do month=1 to 12;
   output;
 end;
 end;
 end;
run;

options missing=0;
proc tabulate data=work.have format=5. classdata=classlevel;
class region county month ;
table Region*(county all) all,month='' all;
keylabel n=' ' all='Total';
run;

[mary_mcneill]

Here is a sample of the Data. Below is a list on the counties that make up each Region. There is no data for Region 1/County Black and Region 2/County White. When I use printmiss, it will display all counties for each Region, even if that county is not associated with that Region (i.e., it displays Counties Blue, Purple, Red, White for Region 1.

Region 1 - Green, Yellow, Orange, Black

Region 2 - Blue, Purple, Red, White

I want the table to display only the counites within each Region, even if there is not any data for that county for a particular year, but not all counties for all Regions.

month	year	county	region
1	2024	GREEN	Region 1
1	2024	GREEN	Region 1
1	2024	YELLOW	Region 1
2	2024	YELLOW	Region 1
3	2024	YELLOW	Region 1
1	2024	ORANGE	Region 1
1	2024	ORANGE	Region 1
10	2024	BLUE	Region 2
11	2024	BLUE	Region 2
11	2024	BLUE	Region 2
1	2024	PURPLE	Region 2
3	2024	PURPLE	Region 2
2	2024	RED	Region 3
2	2024	RED	Region 3
2	2024	RED	Region 3
2	2024	RED	Region 3

[Ksharp]

Still hard to understand your question.

Can you post your desired output ?

Is the following what you are looking for ?

data data;
infile cards expandtabs truncover;
input month	year	county $	region &$20.;
cards;
1	2024	GREEN	Region 1
1	2024	GREEN	Region 1
1	2024	YELLOW	Region 1
2	2024	YELLOW	Region 1
3	2024	YELLOW	Region 1
1	2024	ORANGE	Region 1
1	2024	ORANGE	Region 1
10	2024	BLUE	Region 2
11	2024	BLUE	Region 2
11	2024	BLUE	Region 2
1	2024	PURPLE	Region 2
3	2024	PURPLE	Region 2
2	2024	RED	Region 3
2	2024	RED	Region 3
2	2024	RED	Region 3
2	2024	RED	Region 3
;
proc sql;
create table levels as
select distinct county,region
 from data
union
select 'Black' ,'Region 1'from data(obs=1)
union
select 'Red','Region 2' from data(obs=1)
union
select 'White','Region 2' from data(obs=1)
;

create table classdata as
select * from (select distinct month from data),
                (select distinct year from data),
                (select * from levels)
;
quit;





options missing=0;
proc tabulate data=work.data classdata=classdata format=5.;
class region county month year ;
table Region*(county all) all,month all;
keylabel n=' ' all='Total';
run;