## How to get the percentage for each unique combinations of variables

Solved
Frequent Contributor
Posts: 131

# 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: 8,164

## 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;

All Replies
Posts: 2,125

## 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: 8,164

## 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: 9,370

## 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