Help using Base SAS procedures

How to get the percentage for each unique combinations of variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

How to get the percentage for each unique combinations of variables

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.


Accepted Solutions
Solution
‎02-06-2012 10:24 AM
PROC Star
Posts: 7,492

How to get the percentage for each unique combinations of variables

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;

View solution in original post


All Replies
Trusted Advisor
Posts: 2,116

How to get the percentage for each unique combinations of variables

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

Frequent Contributor
Posts: 131

How to get the percentage for each unique combinations of variables

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;

Solution
‎02-06-2012 10:24 AM
PROC Star
Posts: 7,492

How to get the percentage for each unique combinations of variables

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;

SAS Super FREQ
Posts: 8,869

Re: How to get the percentage for each unique combinations of variables

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;

Frequent Contributor
Posts: 131

Re: How to get the percentage for each unique combinations of variables

Posted in reply to Cynthia_sas

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 177 views
  • 6 likes
  • 4 in conversation