BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wcp_fnfg
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
wcp_fnfg
Obsidian | Level 7

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

16 REPLIES 16
wcp_fnfg
Obsidian | Level 7

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;

Cynthia_sas
SAS Super FREQ

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

wcp_fnfg
Obsidian | Level 7

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

Tobias_N
Calcite | Level 5

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

Ksharp
Super User

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.

wcp_fnfg
Obsidian | Level 7

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

Ksharp
Super User

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

wcp_fnfg
Obsidian | Level 7

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.     

Ksharp
Super User

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

wcp_fnfg
Obsidian | Level 7

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;

Ksharp
Super User

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

wcp_fnfg
Obsidian | Level 7

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. 

Ksharp
Super User

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

wcp_fnfg
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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