Help using Base SAS procedures

Proc SQL count problems.

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 107
Accepted Solution

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?

 


Accepted Solutions
Solution
‎05-16-2018 09:35 AM
Super User
Posts: 10,784

Re: Proc SQL count problems.

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

View solution in original post


All Replies
Super User
Super User
Posts: 9,599

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.

Frequent Contributor
Posts: 107

Re: Proc SQL count problems.

[ Edited ]

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. 

Super User
Super User
Posts: 9,599

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;
Solution
‎05-16-2018 09:35 AM
Super User
Posts: 10,784

Re: Proc SQL count problems.

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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