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

cdubs — Sat, 30 Dec 2017 22:25:47 GMT

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;

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

Reeza — Sat, 30 Dec 2017 23:26:46 GMT

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;

topic Count proportion of observations above a certain value within proc sql? in SAS Procedures

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

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