turn on suggestions

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

Showing results for

Find a Community

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

Topic Options

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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?

Accepted Solutions

Solution

05-16-2018
09:35 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to GKati

05-16-2018 09:31 AM

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to GKati

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

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.

Thx.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to GKati

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;

Solution

05-16-2018
09:35 AM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to GKati

05-16-2018 09:31 AM

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