topic Re: SQL Count not counting in SAS Programming

SQL Count not counting

RoddyJ — Thu, 28 Nov 2019 10:35:20 GMT

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?

Re: SQL Count not counting

PeterClemmensen — Thu, 28 Nov 2019 10:37:53 GMT

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;

Re: SQL Count not counting

ed_sas_member — Thu, 28 Nov 2019 10:46:56 GMT

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.

Re: SQL Count not counting

gamotte — Thu, 28 Nov 2019 11:00:12 GMT

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.