Grouping and counting by ID

[luvscandy27]

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

[maguiremq]

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.

[Ksharp]

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;