BookmarkSubscribeRSS Feed
Ravikumarkummari
Quartz | Level 8

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;

 


actual output.pngdesired output.png
5 REPLIES 5
collinelliot
Barite | Level 11

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. 

collinelliot
Barite | Level 11

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

collinelliot
Barite | Level 11

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

 

 

 

rogerjdeangelis
Barite | Level 11
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
*/


Ksharp
Super User

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;
 
 
 
 

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
  • 5 replies
  • 1102 views
  • 0 likes
  • 4 in conversation