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
- /
- count of table

- Subscribe to 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
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

03-13-2012 03:57 PM

I am trying to figure out how to count the number of items in a table in a proc sql query

the table is like this and has 4796 rows with varying data. The noreadm has no value that is less than 1 and the yesreadm has values that are 0 or 1 only.

prov_tin noreadm yesreadm

1111 42 0

2222 1154 0

3333 15 0

4444 5 1

If I do a count query like this:

proc sql;

create table as tinfinal as

(select

count(noreadm) as noreadm,

count(yesreadm) as yesreadm

from tin);

run;

The output is

noreadm yesreadm

4796 4796

If I do it like this:

proc sql;

create table as tinfinal as

(select

count(noreadm)as noreadm,

count(yesreadm)as yesreadm

from tin

where

noreadm is not null

and

yesreadm = 1);

run;

the output is

noreadm yesreadm

114 114

What I want as the output is

noreadm yesreadm

4796 114

This is the count of all the TINS with data of 4796 and then only the 1's from the yesreadm that are 114. I am not sure how to query it correctly.

Accepted Solutions

Solution

03-14-2012
01:31 AM

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

03-14-2012 01:31 AM

How about :

data have ; input prov_tin noreadm yesreadm ; cards; 1111 42 0 2222 1154 0 3333 15 0 4444 5 1 ; run; proc sql; select count(noreadm) as noreadm, (select count(yesreadm) from have where noreadm is not null and yesreadm = 1 ) as yesreadm from have; quit;

Ksharp

All Replies

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

03-13-2012 04:01 PM

proc sql;

create table tinFinal as

select count(noReadM) as noReadM, sum(yesReadM) as yesReadM

from tin;

PG

PG

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

03-13-2012 04:09 PM

That gave me the yesreadm as 232 and it should only be 114

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

03-13-2012 04:14 PM

Then you must have some values of yesReadM that are not missing, 0 or 1...

Use

sum(yesReadM=1) as yesReadM

then.

PG

PG

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

03-13-2012 04:26 PM

sorry. I misspoke. I should have noreadm at 4796 and yesreadm at 155 some of the TINS have multiple yesreadm counts. I just looked at the table and found some when you said =1 in the above. so i actually see

prov_tin noreadm yesreadm

1111 1456 9

2222 12 0

3333 526 1

4444 5 12

I know there are 4796 unique tins that have 4796 no readmissions. then there (based on looking at the table in excel in descending) that there are 155 unique tins that have a count that is no 0. I need to get a count that shows noreadm 4796 yesreadm 155

I cannot sum because then it gives me 232 and that is what the national readmission total. the national readmissions are based on 1 tick per memberid and they either had a readmission or they did not. so my national count output is

noreadm yesreadm

82578 232

then i have to take the memberid and attach it to their actual admissions based at a TIN level. there might be 2 doctors with different TINS that saw to the 1 memberID's care on the same date therefore that equals 2 ticks of yes for that specific TIN. When I export my SAS stuff to Access and do my count it gives me what my boss says I should have as

noreadm yesreadm

tin exposure 4796 155

ntl exposure 82578 232

I just need to figure out how to calculate the count for TIn so it shows right in SAS so I can do a chisquare off the above. I cannot do that at this point because the code is not pulling right. I tried doing separate steps for noreadm and yesreadm at the tin and then putting in the prov_tin info but it did not work

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

03-13-2012 04:40 PM

What numbers do you want for the four sample rows that posted?

If you want to count when the value is > 0 then you should get counts of 4 and 3 from the posted data.

sum(noreadm>0) as noreadm

,sum(yesreadm>0) as yesreadm

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

03-13-2012 04:41 PM

I'm sorry, I must be too far from the subject matter to understand. Consider this:

COUNT(x) counts the cases where x is not missing

SUM(x) sums the values of x (when x is not missing)

SUM(x=1) counts the cases where x is not missing and x=1

SUM(x=0) counts the cases where x is not missing and x=0

SUM(x>0) counts the cases where x is not missing and x>0

you should be able to get what you want in a single query.

PG

PG

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

03-13-2012 04:19 PM

Sounds like you want to sum instead of count.

This is assuming that yesreadm is 0/1 binary variable. If you count it the zeros will be counted. Only the missing values will not contribute to the count.

select

sum(noreadm) as noreadm

,sum(yesreadm) as yesreadm

from tin

;

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

03-13-2012 04:28 PM

do not want sums. sums make it equal my national output and this is to count the readmissions at a TIN level so I can do a chisquare to get ratios. so if the ntl exposure is noreadm 82578 and yesreadm is 232 of that 232 I have 155 distinct TINS and of those 1 TIN might have 7 readmissions and when I do my chi once I get the data I can get their ratio based on national. i am thinking i might need 3 SAS steps to get what I want

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

03-13-2012 04:31 PM

That sounds like a different question.

So you want to count the distinct ids where the readmit flag is true? Is the definition of a readmission that yesreadm does not equal 0?

count(distinct prov_tin) from tin where yesreadm > 0 ;

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

03-13-2012 04:49 PM

Let me start from the first query that might help

/*****************************get tin level counts for re and nored*********************************/

proc sql;

create table tin as

(select distinct

prov_tin,

count(noreadm) as noreadm,

count(yesreadm) as yesreadm

from claims

group by prov_tin);

run;

the out put is 4796 rows of distinct tins and in the noreadm column there is not 1 value that is 0. it takes and counts all noreadms for that tin from another table. then the yesreadm column does the same by tin and of those I have 4641 rows that have 0 and 155 that have a count that varies from 1 to 9. It will not let me do 2 counts in one query from what I have tried based on my output table above of tin. If I try it and say in a where yesreadm >0 both counts will turn up 155. If i do not put where statement both columns will return 4796. i need it to return

noreadm yesreadm

4796 155

The only way I have gotten this to work now is by doing it like this

proc sql;

create table tin2 as

(select distinct

prov_tin

count(noreadm) as noreadm

from tin

group by prov_tin;

run;

proc sql

create table tin3 as

(select distinct

prov_tin

count(yesreadm) as yesreadm

from tin

where

yesreadm >0

group by prov_tin;

run;

Then I end up with 2 tables. 1 with list of distinct tins that have 4796 rows that are no readms and another table with a list of distinct tins that are 155 rows for yesreadms. but to put together to have a final output of noreadms = 4796 and yesreadms = 155 and i do not need the tins at this point does not work. this is to get me to my final phase of being able to do a chisquare

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

03-13-2012 04:59 PM

then i merge the two with this

data final; set tin2 tin3;

run;

the output is

noreadm yesreadm

4796

155

now all i have to do is figure out how to get rid of that null stuff. I tried an if in the above but it did not get rid of the blank rows. not sure if this will matter when i am doing my chisquare. i am sure there is a way to do all of this in 1 proc sql right now i have 4 proc sql's just to get to this output.

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

03-13-2012 05:37 PM

My guess looking at your question is you need one record per person with a readm yes or no using a datastep and first or last processing.

Then run a proc freq on the data which will give you your chi-sq and table numbers.

If you clarify your original data structure perhaps more advice could be given.

Solution

03-14-2012
01:31 AM

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

03-14-2012 01:31 AM

How about :

data have ; input prov_tin noreadm yesreadm ; cards; 1111 42 0 2222 1154 0 3333 15 0 4444 5 1 ; run; proc sql; select count(noreadm) as noreadm, (select count(yesreadm) from have where noreadm is not null and yesreadm = 1 ) as yesreadm from have; quit;

Ksharp

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

03-14-2012 08:09 AM

That was the ticket. Thanks