Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Home
- /
- Programming
- /
- SAS Procedures
- /
- Proc SQL count problems.

Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

🔒 This topic is **solved** and **locked**.
Need further help from the community? Please
sign in and ask a **new** question.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

Posted 05-16-2018 08:16 AM
(852 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

4 REPLIES 4

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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;

- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content

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

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

**If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. **

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.