topic Re: Proc SQL & Counting in SAS Programming

Proc SQL & Counting

BTAinRVA — Fri, 26 Jul 2013 17:46:56 GMT

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!

Re: Proc SQL & Counting

DBailey — Fri, 26 Jul 2013 18:08:51 GMT

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;

Re: Proc SQL & Counting

BTAinRVA — Fri, 26 Jul 2013 19:44:32 GMT

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.

Re: Proc SQL & Counting

PGStats — Fri, 26 Jul 2013 22:00:07 GMT

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;

Re: Proc SQL & Counting

Fugue — Fri, 26 Jul 2013 22:28:47 GMT

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.

Re: Proc SQL & Counting

BTAinRVA — Mon, 29 Jul 2013 18:41:42 GMT

PG,

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

Re: Proc SQL & Counting

BTAinRVA — Mon, 29 Jul 2013 18:47:07 GMT

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

Re: Proc SQL & Counting

Fugue — Mon, 29 Jul 2013 18:52:39 GMT

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

Re: Proc SQL & Counting

BTAinRVA — Mon, 29 Jul 2013 20:07:13 GMT

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!

Re: Proc SQL & Counting

Fugue — Mon, 29 Jul 2013 20:49:34 GMT

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

Re: Proc SQL & Counting

Reeza — Mon, 29 Jul 2013 20:54:09 GMT

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.

Re: Proc SQL & Counting

BTAinRVA — Mon, 29 Jul 2013 21:24:13 GMT

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!