Date:         Wed, 2 Nov 2005 08:32:06 -0500
Reply-To:     "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Richard A. DeVenezia" <radevenz@IX.NETCOM.COM>
Subject:      Re: Proc SQL simple problem
Comments: To: sas-l@uga.edu

Richard A. DeVenezia wrote:

> You have to summarize a sub-selection.  Or better, just use Proc
> TABULATE for your reporting needs.

Offlist, Kumar asks:
"
To go further into this approach,  suppose I had 5 treatment
groups and I wanted to do the same thing?
"

You have to further nest additional sub-selects.

------------------------------
data survey;
do personId = 1 to 100000;
  if ranuni(123) < 0.25 then gender='M'; else gender='F';
  treatment = 1 + floor(5*ranuni(123));
  output;
end;
run;

proc SQL;
  create table summary as
  select *
       , N / sum(N) * 100 as GenderPctN_OfAll
  from
  (
    select *
         , N / sum(N) * 100 as GenderPctN_OfTreatment

    from
    (
      select gender, treatment, count(gender) as N
      from survey
      group by treatment,gender
    )
    group by treatment
  )
  ;

options nocenter;

filename report temp;
ods html file=report style=sasweb;
proc tabulate data=survey;
  class treatment gender ;
  table treatment all,(gender*(n rowpctn reppctn)) n reppctn ;
run;

ods html close;
------------------------------

Richard A. DeVenezia
http://www.devenezia.com/