Hi Expert,
I wanted to exclude patients who have more than one claim of the drug during the 90 dyas prior to or on the index date.
For example:
data have;
input id $ drug $ indexdate mmddyy10.;
format indexdate mmddyy10.;
datalines;
1 A 10/07/2016
1 B 06/21/2016
2 C 07/27/2017
2 A 09/09/2015
3 A 03/21/2016
3 B 03/21/2016
4 C 07/24/2016
4 B 07/30/2016
5 A 02/23/2016
5 C 05/12/2016
;
run;
Data I want:
1 A 10/07/2016
1 B 06/21/2016
2 C 07/27/2017
2 A 09/09/2015
I wanted to exclude id 3 4 5 because they had two drug claims within 90 days.
Thanks!
You need to read each id twice:
Each mention of HAVE in the SET statement represents one pass of the records for a given ID:
data have;
input id $ drug $ indexdate mmddyy10.;
format indexdate mmddyy10.;
datalines;
1 A 10/07/2016
1 B 06/21/2016
2 C 07/27/2017
2 A 09/09/2015
3 A 03/21/2016
3 B 03/21/2016
4 C 07/24/2016
4 B 07/30/2016
5 A 02/23/2016
5 C 05/12/2016
;
run;
data want (drop=_:);
retain _mindate1 _mindate2;
set have (in=firstpass) have (in=secondpass);
by id;
if first.id then call missing(of _mindate:);
if firstpass=1 then do;
if indexdate=min(indexdate,_mindate1) then do;
_mindate2=_mindate1;
_mindate1=indexdate;
end;
else _mindate2=min(_mindate2,indexdate);
end;
if secondpass=1 and _mindate2-_mindate1>90;
run;
And THANK YOU for posting you data set in almost perfect format. ("Perfect" would have been if you inserted it in a SAS Code popup (use the "running man" icon, but your code was directly copyable and usable.
You don't show any dates other than Indexdate, so I am not sure what you expect to compare with the values of indexdate. OR you need to define how we can tell that a specific date in the example is supposed to be considered an "index date".
Yes, I wanted to compare with the values of indexdate for each subject.
I repeat: HOW do we know which date is the "index date".
You need to read each id twice:
Each mention of HAVE in the SET statement represents one pass of the records for a given ID:
data have;
input id $ drug $ indexdate mmddyy10.;
format indexdate mmddyy10.;
datalines;
1 A 10/07/2016
1 B 06/21/2016
2 C 07/27/2017
2 A 09/09/2015
3 A 03/21/2016
3 B 03/21/2016
4 C 07/24/2016
4 B 07/30/2016
5 A 02/23/2016
5 C 05/12/2016
;
run;
data want (drop=_:);
retain _mindate1 _mindate2;
set have (in=firstpass) have (in=secondpass);
by id;
if first.id then call missing(of _mindate:);
if firstpass=1 then do;
if indexdate=min(indexdate,_mindate1) then do;
_mindate2=_mindate1;
_mindate1=indexdate;
end;
else _mindate2=min(_mindate2,indexdate);
end;
if secondpass=1 and _mindate2-_mindate1>90;
run;
And THANK YOU for posting you data set in almost perfect format. ("Perfect" would have been if you inserted it in a SAS Code popup (use the "running man" icon, but your code was directly copyable and usable.
Thank you for your solution!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.