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.
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;
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
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;
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;
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;
Thanks, Cynthia, for the good examples of using the reporting procedures.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.