Proc SQL & Counting

[BTAinRVA]

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!

[DBailey]

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;

[BTAinRVA]

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.

[PGStats]

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

[BTAinRVA]

PG,

Thanks for the response. That code runs without any warning or errors but Numerator2 and Denominator2 are still coming up all zeros

[Fugue]

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.

[BTAinRVA]

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

[Fugue]

Is EVERY instance of Numerator and Denominator coming up zero? Have you confirmed that that crossings that should not be zero are returning zeros?

[BTAinRVA]

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!

[Fugue]

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).

[BTAinRVA]

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!

[Reeza]

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.