Contributor
Posts: 48

Count proportion of observations above a certain value within proc sql?

Hi all,

I'm trying to create a summary of the overall_days variable, and also giving a proportion of observations where overall_days are above 360 and 730. But the syntax doesn't seem to work quite right... wondering if there's a way around this? Thank you!

proc sql;
create table summary as
select 	min(overall_days) as min,
max (overall_days) as max,
mean (overall_days) as mean,
median (overall_days) as median

count(enrolid) where overall_days>730 / count(enrolid) as proportion_730,
count(enrolid) where overall_days>365 / count(enrolid) as proportion_365

from temp.base;
quit;
Super User
Posts: 23,724

Re: Count proportion of observations above a certain value within proc sql?

[ Edited ]

Yeah, you can't apply WHERE like that within a statement. But you can use the conditional logic trick to get it.

SAS evaluates boolean conditions to 0/1, and then you can sum the 1's to get the number you need.

Overall_Days>730 -> resolves to 0/1 for each record.

Counting it would still give you the total N, but SUM would add up all the 1's.

You could also use a subquery and CASE statements but this seems easier IMO.

sum(overall_days>730) / count(enrolid) as proportion_730

cdubs wrote:

Hi all,

I'm trying to create a summary of the overall_days variable, and also giving a proportion of observations where overall_days are above 360 and 730. But the syntax doesn't seem to work quite right... wondering if there's a way around this? Thank you!

proc sql;
create table summary as
select 	min(overall_days) as min,
max (overall_days) as max,
mean (overall_days) as mean,
median (overall_days) as median

count(enrolid) where overall_days>730 / count(enrolid) as proportion_730,
count(enrolid) where overall_days>365 / count(enrolid) as proportion_365

from temp.base;
quit;

Discussion stats