How to get the percentage for each unique combinations of variables

[bncoxuk]

data work.data;

  input x $ y $ gender $;

  cards;

  1 1 F

  1 2 M

  2 1 F

  2 2 F

  1 2 M

  ;

run;

I want to use PROC SQL to create a table which has 4 columns: x, y, gender and percentage. The percentage represents: number of cases for each unique combination of x, y and gender, divided by the number of observations. So the result should look like:

1 1 F  0.2

1 2 M 0.4

2 1 F  0.2

2 2 F 0.2

Thanks a lot.

[Doc_Duke]

Why use SQL?  FREQ does this directly:

PROC FREQ;

  TABLES x*y*gender/LIST;

  RUN;

You can look up how to output the results into a SAS dataset if you need it that way.

Doc Muhlbaier

Duke

[bncoxuk]

I created the code as below, but don't know how I can calculate the percentage which should be: num/sum. Any idea how can I add this to the code?

proc sql;

  create table work.data02 as

  select x, y, gender, count(gender) as num, (select count(*) from work.data) as sum from work.data

  group by x, y, gender;

quit;

[art297 · Accepted solution]

I agree with Doc that you are doing a lot of unnecessary work, but you could do what you want with some minor adjustment to your code.  e.g.:

proc sql;

  create table work.data02 as

    select x, y, gender,

           count(gender)/(select count(*) from work.data)

            as pct

      from work.data

        group by x, y, gender;

quit;

[Cynthia_sas]

Hi:

  I know this has been answered, but to be fair to PROC REPORT and PROC TABULATE, they can also generate the percentage quite easily (easier than PROC SQL, I think). So I would have chosen TABULATE or REPORT or FREQ depending on whose output look and feel I liked better.

cynthia

ods html file='c:\temp\pctn_examp.html';

proc tabulate data=work.data;

  title '1) TABULATE';

  class x y gender;

  table x*y*gender,pctn;

run;

proc report data=work.data nowd;

  title '2) REPORT';

  column x y gender pctn;

  define x /group;

  define y / group;

  define gender / group;

  define pctn / 'Percentage' f=percent8.2;

run;

proc freq data=work.data;

  tables x*y*gender / list nocum;

  title '3) FREQ';

run;

ods html close;

[bncoxuk]

Thanks, Cynthia, for the good examples of using the reporting procedures.