Working with proc report, I want to only create a subtotal line IFF the group has more than one observation. I don't want to have a single entry group repeated twice...
Some test data:
data tiki;
name='Tiki';
sex='C';
age=10;
height=6;
weight=9.5;
run;
data test;
set sashelp.class tiki;
run;
Normally, I'd get Tiki (Tiki is my cat..) then Subtotal for sex='C'. I want to check if there's more than one obs in that group before I output the subtotal...
I need something like if obs > 1 then break, if new group then reset obs to 1...
I don't think your counter works here because it doesn't seem to reset for each group. Adding the group count before the report gets it done for me:
data tiki;
name='Tiki';
sex='C';
age=10;
height=6;
weight=9.5;
run;
data test;
set sashelp.class tiki;
run;
proc sql;
create table test2 as select
*
from test a
left join (select sex, count(*) as sexcount from test group by sex) b
on a.sex = b.sex;
quit;
proc report data=WORK.TEST2 nowd out=x;
column sexcount Sex Name Weight;
define sexcount /order noprint;
define Sex / group 'Sex' ;
define Name / group 'Name' ;
define Weight / analysis 'Weight';
compute before sex;
length text $ 50;
if sexcount>1 then len = 40; else len = 0;
x=catx(' ',Sex,'SubTotal',': ',weight.sum);
line x $varying50. len;
endcomp;
run;
I want this code, but without the Subtotal C row.
proc report data=WORK.TEST nowd;
column Sex Name Weight;
define Sex / group 'Sex' missing;
compute Sex;
if Sex ne ' ' then hold1=Sex;
if Sex eq ' ' and _break_ eq ' ' then Sex=hold1;
if upcase(_break_)="SEX" then do;
call define("Sex", 'style', 'style=[pretext="Subtotal "]');
end;
if _break_='_RBREAK_' then do;
call define("Sex", 'style', 'style=[pretext="Total"]');
end;
endcomp;
define Name / group 'Name' missing;
compute Name;
if _break_ eq ' ' then do;
if Name ne ' ' then hold2=Name;
end;
endcomp;
define Weight / analysis SUM 'Weight' missing;
break after Sex / summarize;
rbreak after / summarize;
run;
quit;
Hi:
PROC REPORT will not allow you to suppress a BREAK line even if you only have 1 observation in the group. The detail lines are written and then the BREAK line is written. The BREAK line cannot be written conditionally. You can always pre-summarize the data, determine which groups will only have 1 observation and then pass the pre-summarized data to PROC REPORT or use PROC REPORT to do the normal report and then pass the first report for post processing and delete the break lines where the count of detail rows on the report is just 1.
cynthia
I'm still not entirely convinced that this is 100% true. I agree, there's no way to do if X then break after, etc. But inside of a compute block, I still have hope.
As an aside, any thoughts on using a computed variable as a "where" for the output? Similar to "where calculated X" in a proc sql;?
Hi,
I learned that conditionally showing break lines (summaries) is not possible. Is that the same with doing tasks like the following (code does not work for me):
...
define tmp_Break / group noprint;
define Var1 / group 'Group';
...
compute after tmp_Break;
if _break_ eq "TMP_BREAK" and tmp_Break eq 1 then do;
Var1 = "This is the first group";
end;
else if _break_ eq "TMP_BREAK" and tmp_Break eq 0 then do;
Var1 = "This is the second group";
end;
endcomp;
...
The (tmp_Break eq 0/1) condition does not work and I dont know why. I doublechecked the variable so it contains only ones and zeros.
Thanks in advance
Tobias
I am not quite sure . But I think you can get it by VARYING. format. All you need is to post the sample data and what kind of output you need.
You can see the desired output and sample data in the OP and first reply.
OK. Here is .never thought you are just OP . But you need to adjust the style of LINE on your own .
data tiki; name='Tiki'; sex='C'; age=10; height=6; weight=9.5; run; data test; set sashelp.class tiki; run; proc report data=WORK.TEST nowd out=x; column Sex Name Weight; define Sex / group 'Sex' ; define Name / group 'Name' ; define Weight / analysis 'Weight' ; compute weight; if _break_='Sex' and Sex='C' then len=0; else len=40; endcomp; compute after Sex /style(lines)={just=r asis=on} ; x=catx(' ',Sex,'SubTotal',': ',weight.sum); line x $varying50. len; endcomp; compute after /style(lines)={just=r asis=on} ; x=catx(' ','Total',': ',weight.sum); line x $varying50. len; endcomp; run; quit;
Xia Keshan
Two things, this works to select particular categories, but i need to do it based on counts in the category either inside the proc report, or with something like this:
proc sql;
create table test2 as select
*
from test a
left join (select sex, count(*) as sexcount from test group by sex) b
on a.sex = b.sex;
quit;
Also, what do those len\line statements do? I'm not familiar with them.
OK. here is . I have to go to sleep now .It is too late now. Good Luck.
data tiki;
name='Tiki';
sex='C';
age=10;
height=6;
weight=9.5;
run;
data test;
set sashelp.class tiki;
run;
proc report data=WORK.TEST nowd out=x;
column Sex Name Weight;
define Sex / group 'Sex' ;
define Name / group 'Name' ;
define Weight / analysis 'Weight' ;
compute weight;
n+1;
if _break_='Sex' and n=2 then do;len=0; n=0;end;
else len=40;
endcomp;
compute after Sex /style(lines)={just=r asis=on} ;
x=catx(' ',Sex,'SubTotal',': ',weight.sum);
line x $varying50. len;
endcomp;
compute after /style(lines)={just=r asis=on} ;
x=catx(' ','Total',': ',weight.sum);
line x $varying50. len;
endcomp; run;
quit;
Xia Keshan
I don't think your counter works here because it doesn't seem to reset for each group. Adding the group count before the report gets it done for me:
data tiki;
name='Tiki';
sex='C';
age=10;
height=6;
weight=9.5;
run;
data test;
set sashelp.class tiki;
run;
proc sql;
create table test2 as select
*
from test a
left join (select sex, count(*) as sexcount from test group by sex) b
on a.sex = b.sex;
quit;
proc report data=WORK.TEST2 nowd out=x;
column sexcount Sex Name Weight;
define sexcount /order noprint;
define Sex / group 'Sex' ;
define Name / group 'Name' ;
define Weight / analysis 'Weight';
compute before sex;
length text $ 50;
if sexcount>1 then len = 40; else len = 0;
x=catx(' ',Sex,'SubTotal',': ',weight.sum);
line x $varying50. len;
endcomp;
run;
"I don't think your counter works here because it doesn't seem to reset for each group. "
Why did you say so ?
Did you run my code ? I get the desired output ,don't you ?
Correct, your code doesn't really work. It suppresses the 1st line only. If sex for Tiki = 'T', the counter doesn't work. If there's another "single" entry later on, it doesn't work.
Ha. I know where is wrong !
data tiki; name='Tiki'; sex='C'; age=10; height=6; weight=9.5; run; data tiki1; name='Tiki'; sex='T'; age=10; height=6; weight=9.5; run; data test; set sashelp.class tiki tiki1; run; proc report data=WORK.TEST nowd out=x; column Sex Name Weight; define Sex / group 'Sex' ; define Name / group 'Name' ; define Weight / analysis 'Weight' ; compute weight; n+1; if _break_='Sex' and n=2 then len=0; if _break_='Sex' then n=0; else len=40; endcomp; compute after Sex /style(lines)={just=r asis=on} ; x=catx(' ',Sex,'SubTotal',': ',weight.sum); line x $varying50. len; endcomp; compute after /style(lines)={just=r asis=on} ; x=catx(' ','Total',': ',weight.sum); line x $varying50. len; endcomp; run; quit;
Xia Keshan
Yeah, this works. The "line" statement still looks weird on the report, but thanks for the ideas.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.