## Proc SQL count problems.

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?

1 ACCEPTED SOLUTION

Accepted Solutions  Ksharp
Super User

## Re: Proc SQL count problems.

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

4 REPLIES 4

## Re: Proc SQL count problems.

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.

## Re: Proc SQL count problems.

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.

## Re: Proc SQL count problems.

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
Super User

## Re: Proc SQL count problems.

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

Discussion stats
• 4 replies
• 853 views
• 0 likes
• 3 in conversation