The SAS Output Delivery System and reporting techniques

Proc Report - Conditional Subtotal?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

Proc Report - Conditional Subtotal?

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...


Accepted Solutions
Solution
‎03-25-2015 10:35 AM
Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

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;

SAS Super FREQ
Posts: 8,820

Re: Proc Report - Conditional Subtotal?

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

Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

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;?

Occasional Contributor
Posts: 5

Re: Proc Report - Conditional Subtotal?

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

Super User
Posts: 9,878

Re: Proc Report - Conditional Subtotal?

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.

Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

You can see the desired output and sample data in the OP and first reply.

Super User
Posts: 9,878

Re: Proc Report - Conditional Subtotal?

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

Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

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.     

Super User
Posts: 9,878

Re: Proc Report - Conditional Subtotal?

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

Solution
‎03-25-2015 10:35 AM
Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

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;

Super User
Posts: 9,878

Re: Proc Report - Conditional Subtotal?

"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 ?

Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

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. 

Super User
Posts: 9,878

Re: Proc Report - Conditional Subtotal?

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

Frequent Contributor
Posts: 84

Re: Proc Report - Conditional Subtotal?

Yeah, this works.  The "line" statement still looks weird on the report, but thanks for the ideas.     

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 16 replies
  • 1347 views
  • 7 likes
  • 4 in conversation