topic Selecting one record for each ID from many observations using criteria in SAS Programming

Selecting one record for each ID from many observations using criteria

SR11 — Thu, 28 Jan 2021 06:54:09 GMT

Hi,

I have a set of patients and their encounters at different hospitals. So, each patient may have visited different hospitals. So, I want to assign one hospital to each patient based on majority of their visits in the hospital. Some hospital info may be missing. For example-

if a patient have 2, 3 and 4 visits in A, B and C hospital respectively, then C will be assigned to this patient as his hospital as the patient visited C for most of the times.
if a patient have 2, 4 and 4 visits in A, D and E hospital respectively, then D or E(any hospital) will be assigned to this patient as his hospital. But I want to “FLAG’ this patient.

Data I have:

Patient_ID	Hospital
1	A
1	A
1	C
1	B
1	.
2	E
2	F
3	D
3	F
3	B
3	B
4	.
4	.
5	G
5	G

Data I want:

Patient_ID	Hospital	New_hospital	FLAG
1	A	A
1	A	A
1	C	A
1	B	A
1	.	A
2	E	E	1
2	F	E	1
3	D	B
3	F	B
3	B	B
3	B	B
4	.	.
4	.	.
5	G	G
5	G	G

Thank you very much for your support.

Re: Selecting one record for each ID from many observations using criteria

PeterClemmensen — Thu, 28 Jan 2021 06:17:33 GMT

Please explain the exact logic behind when a patient should be flagged?

Re: Selecting one record for each ID from many observations using criteria

SR11 — Thu, 28 Jan 2021 06:57:54 GMT

I am really sorry. I made a typo. I edited and corrected the post.
Here is my explanation for flagging a patient-
For Patient_ID 2, he visited hospital E and F equal times. So I chose any of the hospitals for New_Hospital(in the above example I chose E). And as he visited equal times in both hospitals I flagged it.
Thank you very much.

Re: Selecting one record for each ID from many observations using criteria

Shmuel — Thu, 28 Jan 2021 07:54:32 GMT

Check next code:

data have;
 infile cards ;
 input patient_ID Hospital $;
cards;
1 A
1 A
1 C
1 B
1 .
2 E
2 F
3 D
3 F
3 B
3 B
;run;

proc sql;
  create table freq as
  select patient_ID, Hospital,
    count(hospital) as freq
    from have
    group by Patient_id, Hospital
    order by Patient_ID, freq;
quit;
data flag;
 set freq;
  by Patient_ID;
     if last.Patient_ID;
     *drop freq;  /* unmark to drop the frequency */
run;
data want;
  merge have 
        flag(rename=(hospital=new_hospital));
   by Patient_ID;
      if Hospital= New_Hospital then flag=1;
run;