Count unique patients and overall observation using PROC SQL

Question

Working in SAS but using some SQL code to count the number of unique patients but also the total number of observations for a set of indicators. Each record has a patient identifier, the facility where the patient is, and a group of binary indicators (0,1) for each bed section (the particular place in the hospital where the patient is). For each patient record, only 1 bed section can have a value of '1'. Overall, patients can have multiple observations in a bed section or in other bed sections, i.e. patients can be hospitalized > 1. The idea is to roll this data set up by facility and count the total # of admissions for each bed section but also the total people for each bed section. The people count will always be <= to the observation count. Counting people was just added to my to-do list and to this point I was only summing up observations for each bed section using the code below: proc sql; create table fac_bedsect as select facility, sum(bedsect_alc) as bedsect_alc, sum(bedsect_blind) as bedsect_blind, sum(bedsect_gen) as bedsect_gen from bedsect_type group by facility; quit;

Is there a way I can incorporate into this code the # of unique people for each bed section? Thanks.

It really depends on how your data is structured. Do you have a unique patient identifier variable? Also what is it that your current code is summing? — Tom
What are the variables in bedsect_type ? Is there a person Id ? Is a persons bedsect_* value mutually exclusive, in other words, for a given row with a person id, will only one of the variables bedsect_alc, bedsect_blind, bedsect_gen have a value ? — Richard
Welcome to Stackoverflow, I suggest you read these: Provide a Minimal Complete Verifiable Example (MCVE) and Why should I provide a MCVE. We like to supply answers, but there are methods you can use to get those faster and better. — Paul Maxwell
Does for each bed section mean for each facility ? (i.e. is grouping by facility the only grouping needed?) — Paul Maxwell

Paul Maxwell Paul Maxwell · Accepted Answer · 2018-10-24T23:39:27

With no knowledge of the source table(s) it is impossible to answer precisely, but the syntax for counting distinct values is as seen below. You will need to use the correct column name where I have used "patient_id":

SELECT
    facility
  , COUNT(DISTINCT patient_id) AS patient_count
  , SUM(bedsect_alc)           AS bedsect_alc
  , SUM(bedsect_blind)         AS bedsect_blind
  , SUM(bedsect_gen)           AS bedsect_gen
FROM bedsect_type
GROUP BY
    facility
;

Count unique patients and overall observation using PROC SQL

1 Answers