topic Re: Counting by two variables in SAS Programming

Counting by two variables

wj2 — Sat, 30 Mar 2019 02:16:18 GMT

Hello, I am working with a sample of health record data spanning a one-year period.

I need to know:

1) which providers were full-time, that is, the providers that had at least a total of 416 patient visits (i.e., encounters) in this sample of one year data (416 visits/year = 8 visits/week).

2) the average number of unique patient visits among all full-time providers

I am new to conducting counting procedures in SAS, but I envision first creating a variable that includes the total number of unique patient visits for each full-time provider and then running a proc means to determine the average number among all full-time providers. I have provided an example of how my data set is organized below. Any help with this would be much appreciated!

Patient_id encounter_id visit_provider

111 123 a

112 134 b

112 145 b

113 231 c

114 223 a

115 323 d

116 443 d

117 423 b

......

Re: Counting by two variables

Patrick — Sat, 30 Mar 2019 03:37:00 GMT

@wj2

Here an option using SQL

data have;
  infile datalines dlm=' ' truncover;
  input Patient_id encounter_id visit_provider $;
  datalines;
111 123 a
112 134 b
112 145 b
113 231 c
114 223 a
115 323 d
116 443 d
117 423 b
;
run;

proc sql;
/*  create table want as*/
  select
      visit_provider,
      count(encounter_id) as n_encounters,
      count(encounter_id)/count(distinct patient_id) as avg_encounter_per_patient
  from have
  group by visit_provider
  ;
quit;

Re: Counting by two variables

wj2 — Sat, 30 Mar 2019 16:08:22 GMT

Hi @Patrick,

Thank you for the suggestion! Would you also be able to suggest an option using a data step?

Re: Counting by two variables

Patrick — Sun, 31 Mar 2019 00:25:03 GMT

@wj2 wrote:

Hi @Patrick,

Thank you for the suggestion! Would you also be able to suggest an option using a data step?

Why don't first give it a go yourself and then ask questions accompanied by your code/log should you get stuck.

A data step solution would require to first sort the data and then use if first.<variable>/last.<variable> and just summing up stuff in counter variables (which you retain and also set to 0 at the beginning of a new group).