Please excuse a SQL newbie question but I've searched and haven't found the answer I need. I'm trying to count the occurences where 3 things are true using the code below:
proc sql;
create table rates as
select distinct time, start, end, hsp_id, hsp_state_code, type, count(input(hsp_id,7.)=&id. and input(nexthsp_id,7.)=&id. and readm30=1) as Numerator2 format=comma.,
count(input(hsp_id,7.)=&id.) as Denominator2 format=comma.,
calculated Numerator/calculated Denominator as pct format=percent8.3
from readmabase
group by hsp_id, time
order by hsp_state_code, hsp_id, time;
quit;
I'm trying to count where both hsp_id and nexthsp_id (character variables) equal &id. (a given hospital ID) and readm30 = 1 (numeric variable). When I run the code, Numerator2 and Denominator2 are always the same.
Thanks for any help!
proc sql;
create table rates as
select distinct
time,
start,
end,
hsp_id,
hsp_state_code,
type,
sum(case when input(hsp_id,7.)=&id. and input(nexthsp_id,7.)=&id. and readm30=1 then 1 else 0 end) as Numerator2 format=comma.,
sum(case when input(hsp_id,7.)=&id. then 1 else 0 end) as Denominator2 format=comma.,
calculated Numerator/calculated Denominator as pct format=percent8.3
from readmabase
group by time,start,end,hsp_id,hsp_state_code,type
order by hsp_state_code, hsp_id, time;
quit;
DBailey,
Thanks for the reply. I think that's what I'm looking for. However when I run it, the variables Numerator2 and Denominator2 all come up zeros.
I get a note: Invalid string. Invalid argument to function INPUT.
You could try :
proc sql;
create table rates as
select
time,
start,
end,
hsp_id,
hsp_state_code,
type,
sum(hsp_id="&id." and nexthsp_id="&id." and readm30=1) as Numerator2 format=comma.,
sum(hsp_id="&id.") as Denominator2 format=comma.,
calculated Numerator/calculated Denominator as pct format=percent8.3
from readmabase
group by time,start,end,hsp_id,hsp_state_code,type
order by hsp_state_code, hsp_id, time;
quit;
PG
PG,
Thanks for the response. That code runs without any warning or errors but Numerator2 and Denominator2 are still coming up all zeros
1. You state hsp_id and nexthsp_id are character, and your code suggests they can be readily converted to numbers. But, if there are non-numeric characters (e.g. letters A-Z), then the input function will return an error. Are there also hidden characters that may cause a problem?
I don't think you need to convert hsp_id and nexthsp_id to numeric values. You can accomplish the same result by leaving convert hsp_id and nexthsp_id as text and enclosing your macro parm in quotes, i.e. '&id'.
2. Does your macro variable &id have enclosing quotes? The following two statements will be interpreted quite differently:
%let id = '123';
%let id = 123;
3. The combination of your grouping function (the correct grouping clause is suggested by Dbailey given your code example) and the case statements (SUM(CASE . . . )) will return 0s whenever there are no observations that satisfy the case statement for a given crossing. So, if there are no observations for a given crossing of time-start-end-hsp_id-hsp_state_code-type where hsp_id=&id. and nexthsp_id=&id. and readm30=1, then the numerator will be 0. Similarly, the denominator will be 0 whenever there are no matches of hsp_id=&id for a given crossing of time-start-end-hsp_id-hsp_state_code-type.
4. If you want your output to only include crossings with non-zero denominators, then add a where statement (e.g. where hsp_id=&id) and you can simplify the expression for the numerator var more simply (e.g. count ( * ) as numerator).
It would also be helpful if we had some sample data and a sample of what you want your output to look like.
Fuque,
Thanks for the response. The variables hsp_id and nexthsp_id could possibly have non-numeric characters. It would be best to compare them as character strings rather than numbers.
The macro variable id does not have quotes
Is EVERY instance of Numerator and Denominator coming up zero? Have you confirmed that that crossings that should not be zero are returning zeros?
PG & Fuque,
I apologize, they aren't all coming up zeros. The Numerator and Denominator are all zeros except for the given hsp_id I gave it with the variable id. So it's doing exactly what I told it to do but not what I really want it to do. I'll have to give this a second thought. What I want to do is sum the quarterly 30-day readmissions for each hsp_id (Denominator) and the sum the number of those readmissions that went back to that same hsp_id (Numerator).
Thanks again for all your help!
It would be helpful to have a decent sample of the input data with a bit of a data dictionary, and a layout of what you want the desired output to look like (with definitions of calculated elements).
Fugue,
I should be able to figure it out from here. The big part I was unsure of was how to compare numbers and non-number character strings, which you answered in your first post. Thanks again!
Variable names are wrong but here's a hint or starting point.
if hospid=next hospid then you want to count for numerator.
sum(hospid=nexhospid) in sql logic.
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 how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.