Proc SQL count problems.

[GKati]

Hello SAS users,

I'm running the following code:

data t0;
input Estimate Numord;
datalines;

2447 1
42 2
10 3
-39 4
-84 5
;
run;


data t2;
input beta_3;
datalines;

19
42
10
-42
-76
;
run;

proc sql;
  select count(a.beta_3<=b.Estimate)/count(a.beta_3) into :z0bar
  from t2 a,
  t0 b
  where b.numord=3
  ;
run;

%put _all_;

I am expecting 0.4 for z0bar, but I am getting 1. Why?

[RW9]

What is it your trying to achieve?  The reason is, if you look at a table version of your data:

proc sql;
  create table tmp as
  select a.beta_3,b.estimate,a.beta_3<=b.Estimate as test,count(a.beta_3<=b.Estimate)/count(a.beta_3) as a format=best.
  from t2 a,
  t0 b
  where b.numord=3
  ;
run;

You will see that there is 5 rows with a 0 or 1 for each beta_3<=estimate, and the count(beta_3) will always be 1, as there is exactly 1 per row, 0/1=0 and 1/1=1, you get a 1 out.

[GKati]

I'm trying to count the number of rows in b that are <= the value of the variable Estimate at numord=3, as a percentage of all the rows in b. 

Thx. 

[RW9]

Something like (and this is verbose just to show working out):

  create table tmp as
  select a.*,
         b.tot,
         (select count(*) from t2 where beta_3 <= a.estimate) as sub,
         (calculated sub / b.tot) * 100  as percent
  from  (select * from t0 where numord=3) a
  left join (select count(*) as tot from t2) b
  on    1=1;
run;

[Ksharp · Accepted solution]

 select sum(a.beta_3<=b.Estimate)/count(a.beta_3) into :z0bar