BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

14 REPLIES 14
PGStats
Opal | Level 21

proc sql;

create table tinFinal as

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

from tin;

PG

PG
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

PGStats
Opal | Level 21

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

Use

sum(yesReadM=1) as yesReadM

then.

PG

PG
tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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

PGStats
Opal | Level 21

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
Tom
Super User Tom
Super User

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

;

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

Tom
Super User Tom
Super User

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 ;

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

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.

Reeza
Super User

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.

Ksharp
Super User

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

tmm
Fluorite | Level 6 tmm
Fluorite | Level 6

That was the ticket. Thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 1245 views
  • 0 likes
  • 5 in conversation