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

[cdubs]

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;

[Reeza]

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;

---