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.
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
proc sql;
create table tinFinal as
select count(noReadM) as noReadM, sum(yesReadM) as yesReadM
from tin;
PG
That gave me the yesreadm as 232 and it should only be 114
Then you must have some values of yesReadM that are not missing, 0 or 1...
Use
sum(yesReadM=1) as yesReadM
then.
PG
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
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
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
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
;
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
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 ;
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
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.
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.
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
That was the ticket. Thanks
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.