topic Re: Proc Report - Conditional Subtotal? in ODS and Base Reporting

Proc Report - Conditional Subtotal?

wcp_fnfg — Mon, 23 Mar 2015 16:49:15 GMT

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

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Mon, 23 Mar 2015 17:03:41 GMT

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;

Re: Proc Report - Conditional Subtotal?

Cynthia_sas — Tue, 24 Mar 2015 01:52:25 GMT

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

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Tue, 24 Mar 2015 16:38:11 GMT

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

Re: Proc Report - Conditional Subtotal?

Ksharp — Wed, 25 Mar 2015 13:51:32 GMT

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.

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Wed, 25 Mar 2015 13:53:18 GMT

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

Re: Proc Report - Conditional Subtotal?

Ksharp — Wed, 25 Mar 2015 14:06:25 GMT

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

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Wed, 25 Mar 2015 14:14:16 GMT

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.

Re: Proc Report - Conditional Subtotal?

Ksharp — Wed, 25 Mar 2015 14:31:46 GMT

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

Re: Proc Report - Conditional Subtotal?

Ksharp — Wed, 25 Mar 2015 14:34:51 GMT

varying is a varying length format , if its length is 0 (i.e. len=0 in my code) , it will not print that variable x (line x $varying50. len;).

Search it at support.sas.com to find more about it .

Xia Keshan

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Wed, 25 Mar 2015 14:35:55 GMT

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;

Re: Proc Report - Conditional Subtotal?

Ksharp — Wed, 25 Mar 2015 14:39:16 GMT

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

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Wed, 25 Mar 2015 15:19:11 GMT

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.

Re: Proc Report - Conditional Subtotal?

Ksharp — Thu, 26 Mar 2015 12:01:26 GMT

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

Re: Proc Report - Conditional Subtotal?

wcp_fnfg — Thu, 26 Mar 2015 12:47:11 GMT

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

Re: Proc Report - Conditional Subtotal?

Ksharp — Thu, 26 Mar 2015 12:50:07 GMT

Yeah. You need to adjust its style on your own. Maybe will cost you much time .

Re: Proc Report - Conditional Subtotal?

Tobias_N — Mon, 04 May 2015 09:52:20 GMT

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