topic Re: Identifying observations for an ID after specific date identified within the same ID. in SAS Programming

Identifying observations for an ID after specific date identified within the same ID.

ttbtink — Tue, 03 Nov 2020 17:57:56 GMT

Sorry if the title is confusing, I have a hard to articulating the problem in a short title. Currently, my data set has a mix of healthcare visits that are primary care(pc) and pain specialists (ps). I am able to identify the FIRST primary care visit for each patient. What I want to do is then identify the next pain specialist visit, in actual time, for that specific patient. I can either delete all pain visits that are before that primary care visit or just create a dummy variable. I do not know how to code for this at all. Below I have the primary care (pc) visit that I already have identified and serves as the starting point (by date) to identify the next pain specialist visit (ps).

Example:

ID	visit	date	keep
1	ps	11/23/19	0
1	ps	12/1/19	0
1	pc	12/15/19	1*
1	ps	1/3/20	1
2	ps	5/23/19	0
2	pc	6/25/19	1*
2	ps	7/3/19	1
3	pc	3/21/20	1
3	ps	4/2/20	1

* already identified

Re: Identifying observations for an ID after specific date identified within the same ID.

novinosrin — Tue, 03 Nov 2020 18:09:31 GMT

I would rather give you the boolean 1 or 0's flag. Feel free to filter at your own convenience-



data have;
input ID	visit $	date :mmddyy10.;
format date mmddyy10.;
cards;
1	ps	11/23/2019
1	ps	12/1/2019
1	pc	12/15/2019
1	ps	1/3/2020
2	ps	5/23/2019
2	pc	6/25/2019
2	ps	7/3/2019
3	pc	3/21/2020
3	ps	4/2/2020
;

data want;
 if 0 then set have;
 do flag=0 by 0 until(last.id);
  set have;
  by id;
  if visit='pc' then flag=1;
  output;
 end;
run;

Re: Identifying observations for an ID after specific date identified within the same ID.

novinosrin — Tue, 03 Nov 2020 18:12:06 GMT

And the boring traditional text book RETAIN-

data want;
 set have;
 by id;
 retain flag;
 if first.id then flag=0;
 if   visit='pc' then flag=1;
run;

Re: Identifying observations for an ID after specific date identified within the same ID.

novinosrin — Tue, 03 Nov 2020 18:25:44 GMT

And this one , dedicating my gratitude to Gurus @hashman , @data_null__ and @SASKiwi for teaching me "how to master/play with boolean expressions" and tolerating my silly questions many times




data have;
input ID	visit $	date :mmddyy10.;
format date mmddyy10.;
cards;
1	ps	11/23/2019
1	ps	12/1/2019
1	pc	12/15/2019
1	ps	1/3/2020
2	ps	5/23/2019
2	pc	6/25/2019
2	ps	7/3/2019
3	pc	3/21/2020
3	ps	4/2/2020
;

proc sql;
 create table want as
 select *,date>=min(ifn(visit='pc',date,.)) as flag 
 from have
 group by id
 order by id,date;
quit;

Re: Identifying observations for an ID after specific date identified within the same ID.

ttbtink — Tue, 03 Nov 2020 19:39:39 GMT

Thank you! This was straightforward and easy to follow. I appreciate all the help. 🙂

Re: Identifying observations for an ID after specific date identified within the same ID.

ttbtink — Tue, 03 Nov 2020 19:43:57 GMT

Thank you. I swear I am not a total noob to SAS in general, but I have my limits and do not know the more advanced coding techniques. I use this forum a lot of answer most my questions, and I always find an answer. I just couldn't find a solution for this specific problem. You were quick and helpful!