topic Re: Grouping and counting by ID in SAS Programming

Grouping and counting by ID

luvscandy27 — Tue, 07 Sep 2021 18:13:35 GMT

Hello everyone,

I have a question that I can not for the life of me figure out. I have the following sample dataset.

ID	appt_type	qtr_1
1852558	Specialty	1
1852558	Future	.
1852558	Future	.
1852558	Future	.
0001D9DA82	Future	.
00026A2508	Specialty	1

What I would like to do is keep the ids that have qtr_1 = 1 and get a count of how many

appt_types happened after the first appt_type. If an id has qtr_1 = 1 and but only has one appt_type then that id can be excluded.

From the sample data above, I am trying to achieve this outcome:

ID	appt_type	qtr_1	count
1852558	Specialty	1
1852558	Future	.	1
1852558	Future	.	2
1852558	Future	.	3

Re: Grouping and counting by ID

maguiremq — Tue, 07 Sep 2021 18:44:48 GMT

data have;
infile datalines truncover;
input ID :$10. appt_type :$10. qtr_1;
datalines;
1852558 	Specialty 	1
1852558 	Future 	.
1852558 	Future 	.
1852558 	Future 	.
0001D9DA82 	Future 	.
00026A2508 	Specialty 	1
;
run;

proc sort
	data = have;
		by id descending qtr_1;
run;

data want;
	set have;
	by id descending qtr_1;
		if first.id and first.qtr_1 = 1 then call missing(count);
			else count + 1;
		if (first.id and last.id and qtr_1 = 1) or (qtr_1 = . and count = .) then delete;
run;

Obs 	ID 	appt_type 	qtr_1 	count
1 	1852558 	Specialty   1   .
2 	1852558 	Future 	     . 	1
3 	1852558 	Future 	     . 	2
4 	1852558 	Future 	     . 	3

May run into issues depending on the complexity of your underlying data.

Re: Grouping and counting by ID

Ksharp — Wed, 08 Sep 2021 12:56:19 GMT

data have;
infile datalines truncover;
input ID :$10. appt_type :$10. qtr_1;
datalines;
1852558 	Specialty 	1
1852558 	Future 	.
1852558 	Future 	.
1852558 	Future 	.
0001D9DA82 	Future 	.
00026A2508 	Specialty 	1
;
run;

data temp;
 set have;
 by id appt_type notsorted;
 if first.appt_type then n=0;
 n+1;
 if appt_type='Specialty' then n=.;
run;
proc sql;
create table want as
select * from temp group by id having count(distinct appt_type)>1 order by id,appt_type desc,n;
quit;