topic How to get the percentage for each unique combinations of variables in SAS Procedures

How to get the percentage for each unique combinations of variables

bncoxuk — Mon, 06 Feb 2012 15:00:24 GMT

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.

How to get the percentage for each unique combinations of variables

Doc_Duke — Mon, 06 Feb 2012 15:07:58 GMT

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

How to get the percentage for each unique combinations of variables

bncoxuk — Mon, 06 Feb 2012 15:14:38 GMT

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;

How to get the percentage for each unique combinations of variables

art297 — Mon, 06 Feb 2012 15:24:27 GMT

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;

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

Cynthia_sas — Mon, 06 Feb 2012 16:13:18 GMT

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;

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

bncoxuk — Mon, 06 Feb 2012 16:22:07 GMT

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