CODEWITHSUNDEEP
sasproc-sql
Brian Bartle
Brian Bartle

Reputation: 53

Count unique patients and overall observation using PROC SQL

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.

Upvotes: 1

Views: 1191

Answers (1)

Paul Maxwell
Paul Maxwell

Reputation: 35603

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
;

Upvotes: 3

Related Questions