topic Re: condensing data down to one row per patient ID, encounter ID, date/time combination in SAS Programming

condensing data down to one row per patient ID, encounter ID, date/time combination

hein68 — Tue, 19 Oct 2021 13:57:29 GMT

Hello. I have data like this (see below), but it has over 4,000 patientID, encounterID, and date/time combinations. I want to be able to write code to condense the data down to 1 row per patientID, encounterID, date/time combination.

patientID	encounterID	Date/time	variable1	variable2	variable3	variable4	variable5
1	333	7/11/2016 19:01				6
1	333	7/12/2016 19:01
1	333	7/13/2016 19:01	13
1	333	7/14/2016 19:01			15
1	333	7/15/2016 19:01
1	333	7/16/2016 19:01
1	333	7/17/2016 19:01					8
1	333	7/18/2016 19:01		24
1	333	7/19/2016 19:01
1	333	7/20/2016 19:01

So in the example above I would end up with 1 row like this (see below), but I want to use simple code to handle all of the 4,000+ combinations of patientID, encounterID, and date/time.

patientID	encounterID	Date/time	variable1	variable2	variable3	variable4	variable5
1	333	7/11/2016 19:01	13	24	15	6	8

Thanks!

Re: condensing data down to one row per patient ID, encounter ID, date/time combination

PaigeMiller — Tue, 19 Oct 2021 14:10:54 GMT

proc summary data=have nway;
     class patientid encounterid date_time;
     var variable1-variable5;
     output out=want sum=;
run;

Re: condensing data down to one row per patient ID, encounter ID, date/time combination

maguiremq — Tue, 19 Oct 2021 14:13:32 GMT

This assumes your data is sorted by the BY statement I have in there.

proc means
	data = have noprint;
	by patientid encounterid datetime;
	var variable:;
	output out = want (drop = _:)
		max() = ;
run;

Produces something like this (note: I only reproduced part of your code since it wasn't in the DATALINES format.):

patientid encounterid variable1 variable2 variable3 variable4 variable5 
1 333 13 24 15 6 8

Re: condensing data down to one row per patient ID, encounter ID, date/time combination

hein68 — Tue, 19 Oct 2021 15:44:03 GMT

That worked, thanks!