05-16-2018 08:16 AM
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?
05-16-2018 08:54 AM
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.
05-16-2018 09:02 AM - edited 05-16-2018 09:03 AM
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.
05-16-2018 09:20 AM
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;
Need further help from the community? Please ask a new question.