The SAS Output Delivery System and reporting techniques

Proc Report avoid single group wise frequency

Reply
Frequent Contributor
Posts: 111

Proc Report avoid single group wise frequency

Hi all,

 

I have the data of Age, Sex, Height, assume age is character variable.

In proc report taken group variabe as age, this age i need to display their gender and sum of height.

Actual report and final output is attached in image format.

 

what i need is if group variable contains single row then instead of showing that row, group variable is sufficient.

 

I followed below code for this, but there is lot of steps in my code.

Is there any method to reduce the code in proc report it self.

 

 

proc sql;
create table class_mod as
select distinct *,
				count(age) as count
from sashelp.class
group by age;
quit;

data class;
retain age1 sex height;
set class_mod;
drop age;
age1=input(age,$20.);
rename age1=age;
keep age1 sex height count;
where age in (14,15,16);
run;

proc report data=class nowd list out=report_class;
column  age sex height count;
define age/ group;
DEFINE  Height / SUM;

break after age/summarize ;
compute after age;
age=catx('-','Avg-value for',age);
endcomp;
rbreak after/summarize;
compute after;
age='Grand total';
endcomp;
run;

data report_class;
set report_class;
if count=1 and _BREAK_=" " then delete;
run;

proc report data=report_class nowd list out=report_class1;
column  age sex height;
define age/ group order=data;
run;

 

Attachment
Attachment
Super Contributor
Posts: 259

Re: Proc Report avoid single group wise frequency

I don't know if this can be done within the proc itself. I don't think so. If nobody else drops some knowledge on this, I'd recommend doing the pre-summarization yourself and deleting the extraneous column to get the desired effect. 

Super Contributor
Posts: 259

Re: Proc Report avoid single group wise frequency

Also, your output looks like it's summing the age, not averaging. I'm assuming that's an error, correct?

Super Contributor
Posts: 259

Re: Proc Report avoid single group wise frequency

I'm also not clear on why you have the repeatd M M and F F within an age. Sorry for all the questions!

 

Formatting, tweaks aside, my assumption is that you're looking for something like this:

 

xx.png

 

 

 

Valued Guide
Posts: 505

Re: Proc Report avoid single group wise frequency

WPS/SAS: Using SQL union to create sections of a report

You do not need WPS for this( Ijust like to check WPS)

Using SQL union to create sections of a report

You may not like this solution but I find it much more flexible to
preprocess to get the data yhe way you want it and then use
just a dumb proc report ot proc print.


inspired by
https://goo.gl/cUK8u1
https://communities.sas.com/t5/ODS-and-Base-Reporting/Proc-Report-avoid-single-group-wise-frequency/m-p/339709


HAVE (subset of sashelp.class of 14-16 year olds)
===================================================

Up to 40 obs WORK.CLASS total obs=9

Obs    SEX    AGE    HEIGHT

 1      M      14     69.0
 2      F      14     62.8
 3      M      14     63.5
 4      F      15     62.5
 5      F      14     64.3
 6      F      15     66.5
 7      M      16     72.0
 8      M      15     67.0
 9      M      15     66.5

WANT

Up to 40 obs WORK.WANT total obs=12
===================================

Obs    MJR             SEX     HEIGHT

  1    14               F      62.800
  2                     F      64.300
  3                     M      63.500
  4                     M      69.000
  5    Avg-value=14            64.900
  6    15               F      62.500
  7                     F      66.500
  8                     M      66.500
  9                     M      67.000
 10    Avg-value=15            65.625
 11    Avg-value=16            72.000
 12    Grand total            594.100

WORKING CODE
============


   select case when count(*)=1 then 'ZZZ' else 'IND' end as grp, age, sex, height from class group by age, sex
   union
   select 'MEA' as grp, age, 'Q' as sex, mean(height) as height from class group by age
   union
   select 'SUM' as grp, 99 as age, 'Z' as sex, sum(height) as height from class

SOLUTION
========

proc datasets lib=work kill;
run;quit;

* create some data;
options validvarname=upcase;
data class;
  set sashelp.class(where=(age>13) drop=name weight);
run;quit;

%utl_submit_wps64('
libname wrk "%sysfunc(pathname(work))";
proc sql;
   create table wrk.rptpre(where=( grp ne "ZZZ")) as
   select case when count(*)=1 then "ZZZ" else "IND" end as grp, age, sex, height from wrk.class group by age, sex
   union
   select "MEA" as grp, age, "Q" as sex, mean(height) as height from wrk.class group by age
   union
   select "SUM" as grp, 99 as age, "Z" as sex, sum(height) as height from wrk.class
   order by age
;quit;
');

/*
Up to 40 obs WORK.RPTPRE total obs=12

Obs    GRP    AGE    SEX     HEIGHT

  1    IND     14     F      62.800
  2    IND     14     F      64.300
  3    IND     14     M      63.500
  4    IND     14     M      69.000
  5    MEA     14     Q      64.900
  6    IND     15     F      62.500
  7    IND     15     F      66.500
  8    IND     15     M      66.500
  9    IND     15     M      67.000
 10    MEA     15     Q      65.625
 11    MEA     16     Q      72.000
 12    SUM     99     Z     594.100
*/

%utl_submit_wps64('
libname wrk "%sysfunc(pathname(work))";
data wrk.want;
  length mjr $32;
  retain mjr " ";
  set wrk.rptpre;
  by age;
  select;
    when ( first.age and grp="IND") mjr=put(age,2.);
    when ( last.age  and grp="MEA") do; mjr=catx("=","Avg-value",put(age,2.)); sex="";end;
    when ( grp="SUM")               do; mjr="Grand total";sex=" ";end;
    otherwise do;mjr=" ";end;
  end;
  keep mjr sex height;
run;quit;
');

/*
Up to 40 obs WORK.WANT total obs=12

Obs    MJR             SEX     HEIGHT

  1    14               F      62.800
  2                     F      64.300
  3                     M      63.500
  4                     M      69.000
  5    Avg-value=14            64.900
  6    15               F      62.500
  7                     F      66.500
  8                     M      66.500
  9                     M      67.000
 10    Avg-value=15            65.625
 11    Avg-value=16            72.000
 12    Grand total            594.100
*/


Grand Advisor
Posts: 9,320

Re: Proc Report avoid single group wise frequency

A workaround way is using LINE + $varying. 

But that might not be look like what you want.

 

 

data have;
 set sashelp.class;
 keep age sex height;
 where age in (14,15,16);
run;
proc sort data=have;by age;run;
data want;
length char_age $ 40;
 set have end=last;
 by age;
 array all{99999} _temporary_;
 array h{9999} _temporary_;
 if first.age then do;
  call missing(of h{*});i=0;
  char_age=put(age,best.);
 end;
 i+1;
 h{i}=height;
 n+1;
 all{n}=height;
 if not last.age then output;
  else do;
   if i=1 then do;char_age=cat('avg value for',age);sex=' ';output;end;
    else do;
      output;
      char_age=cat('avg value for',age);
      sex=' ';
      height=sum(of h{*});
      output;
    end;
  end;
 
 if last then do;
      char_age='Grand total:';
      sex=' ';
      height=sum(of all{*});
      output;
 end;
 drop i n age;
run;

proc print noobs;run;
 
 
 
 
Post a Question
Discussion Stats
  • 5 replies
  • 154 views
  • 0 likes
  • 4 in conversation