Help using Base SAS procedures

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

Reply
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;

 

Ask a Question
Discussion stats
  • 1 reply
  • 154 views
  • 1 like
  • 2 in conversation