BookmarkSubscribeRSS Feed
BTAinRVA
Quartz | Level 8

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!

11 REPLIES 11
DBailey
Lapis Lazuli | Level 10

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
Quartz | Level 8

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
Opal | Level 21

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
BTAinRVA
Quartz | Level 8

PG,

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

Fugue
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1848 views
  • 6 likes
  • 5 in conversation