USING TWO DIFFERENT CONDITIONS IN 1 PROC SQL STEP

[d6k5d3]

I have the following dataset:

data have;
  input Sl  Return;
datalines;
 1  4.5563
 2  4.8562
 3  0.0000
 4  3.5879
 5  0.0000
 6  0.0000
 7  0.0000
 8  0.0000
 9  2.5879
10  3.6879
run;

I want to do the following:

proc sql;

select count (return if return= 0) as zero_obs, zero_obs/count (return if return ne 0) as zero_perc from have;

quit;

Not getting it.

Help please.

Much thanks.

[LinusH]

Instead om count(), use sum. In the expression use CASE, it's the SQL counterpart to IF.

sum(case return when 0 then 1 else 0 end)

[Tom · Accepted solution]

Your syntax looks really strange (what languages uses "RETURN X IF Y" syntax?) and also the logic of your formula seems off.

In SAS it is simple because logical expressions evaluate to 0 or 1.

proc sql ;
select sum( return=0 ) as n_zero
     , count(*) as n 
     , calculated n_zero / calculated n as percent_zero
from have
;
quit;

In other flavors of SQL you will want to use a CASE statement.  Also since other languages do not have the CALCULATED keyword you might need to replicate the formulas or use a nested query to get both the count and the percentage in one query.