Hi all,
I'm trying to use proc SQL to calculate a percentage of entries in a dataset.
I'm using the following code:
proc SQL; create table percents as select *, count(pta = "Yes")/count(*) as pct_pta,
count(pta = "Yes") as count_pta from d1; quit;
The count(pta = "Yes") is counting every entry in the dataset, the pta = "Yes" condition isn't working properly.
The dataset has a column pta where the value is either "Yes" or missing, I tired changing it to "Yes" or "No" and 0 or 1, but still no luck.
Can anyone help me out?
Using your own logic, a quick fix could be this
data d1;
pta='Yes'; output;
pta='No'; output;
pta='Yes'; output;
pta='Yes'; output;
run;
proc SQL;
create table percents as
select *,
sum(pta = "Yes")/count(*) as pct_pta,
sum(pta = "Yes") as count_pta
from d1;
quit;
Using your own logic, a quick fix could be this
data d1;
pta='Yes'; output;
pta='No'; output;
pta='Yes'; output;
pta='Yes'; output;
run;
proc SQL;
create table percents as
select *,
sum(pta = "Yes")/count(*) as pct_pta,
sum(pta = "Yes") as count_pta
from d1;
quit;
Hi @RoddyJ
Depending on the output you want, you can also remove *, in you code. It will output only the percentage and frequency you want.
Otherwise, the method proposed by @PeterClemmensen will output as many rows as input dataset with duplicate information regarding percentage and frequency.
Hello,
count function gives you the number of observations for the variable in the resulting dataset, no matter
what particular values the variable takes.
data have;
input dummy;
cards;
0
1
1
0
0
0
1
0
;
quit;
proc sql;
SELECT count(dummy) FROM have;
quit;
Your condition (pta = "Yes") creates an implicit dummy variable as the one in the example above.
When pta <> "Yes" the observation will be zero but will be counted anyway.
=> You have to use the sum function as @PeterClemmensen suggests.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.