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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.