Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Proc SQL & Counting

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

07-26-2013 01:46 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BTAinVA

07-26-2013 02:08 PM

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;

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to DBailey

07-26-2013 03:44 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BTAinVA

07-26-2013 06:00 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

07-29-2013 02:41 PM

PG,

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BTAinVA

07-26-2013 06:28 PM

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.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Fugue

07-29-2013 02:47 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BTAinVA

07-29-2013 02:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Fugue

07-29-2013 04:07 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BTAinVA

07-29-2013 04:49 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Fugue

07-29-2013 05:24 PM

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!

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to BTAinVA

07-29-2013 04:54 PM

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.