Flag all observations on the first visit date

[JLang055]

Hello,

I have a patient dataset with observations across many years. I would like to flag all the observations on the first visit date. If there are multiple diagnoses on the first visit date I would like to also flag those but only if the diagnoses are different. I would like to create a second flag for those with multiple different diagnoses on the first date. See below for an example.

FID	Date	diagnosis	flag (want)	Multi (want)
1	2016-05-05	anxiety	1	0
1	2016-05-05	anxiety	0	0
1	2016-07-13	ADHD	0	0
1	2016-07-18	Conduct	0	0
2	2017-05-29	conduct	1	0
3	2017-08-16	anxiety	1	0
3	2017-10-18	adhd	0	0
4	2017-11-16	conduct	1	1
4	2017-11-16	adhd	1	0
4	2017-12-10	anxiety	0	0

[Tom · Accepted solution]

I am not sure I understand what the flags mean clinically but here is a method.

Basically you need to count the number of dates per FID to be able to tell if the diagnosis is on the first date for that FID.

data have;
  input FID Date :yymmdd. diagnosis :$upcase. flag_want multi_want ;
  format date yymmdd10.;
cards;
1 2016-05-05 anxiety 1 0
1 2016-05-05 anxiety 0 0
1 2016-07-13 ADHD 0 0
1 2016-07-18 Conduct 0 0
2 2017-05-29 conduct 1 0
3 2017-08-16 anxiety 1 0
3 2017-10-18 adhd 0 0
4 2017-11-16 conduct 1 1
4 2017-11-16 adhd 1 0
4 2017-12-10 anxiety 0 0
;

proc sort;
 by fid date diagnosis ;
run;

data want ;
  set have;
  by fid date diagnosis  ;
  if first.fid then dateno=0;
  dateno+first.date;
  flag=first.diagnosis and dateno=1;
  multi=first.diagnosis and dateno=1 and not first.date ;
run;

proc print;
run;

                                         flag_    multi_
Obs    FID          Date    diagnosis     want     want     dateno    flag    multi

  1     1     2016-05-05     ANXIETY       1         0         1        1       0
  2     1     2016-05-05     ANXIETY       0         0         1        0       0
  3     1     2016-07-13     ADHD          0         0         2        0       0
  4     1     2016-07-18     CONDUCT       0         0         3        0       0
  5     2     2017-05-29     CONDUCT       1         0         1        1       0
  6     3     2017-08-16     ANXIETY       1         0         1        1       0
  7     3     2017-10-18     ADHD          0         0         2        0       0
  8     4     2017-11-16     ADHD          1         0         1        1       0
  9     4     2017-11-16     CONDUCT       1         1         1        1       1
 10     4     2017-12-10     ANXIETY       0         0         2        0       0

[Ksharp]

data have;
  input FID Date :yymmdd. diagnosis :$upcase.   ;
  format date yymmdd10.;
cards;
1 2016-05-05 anxiety 1 0
1 2016-05-05 anxiety 0 0
1 2016-07-13 ADHD 0 0
1 2016-07-18 Conduct 0 0
2 2017-05-29 conduct 1 0
3 2017-08-16 anxiety 1 0
3 2017-10-18 adhd 0 0
4 2017-11-16 conduct 1 1
4 2017-11-16 adhd 1 0
4 2017-12-10 anxiety 0 0
;

proc sql;
create table temp as
select *,count(*) as a,count(distinct diagnosis) as b
 from have
   group by fid,date  ;
quit;
data want; 
 set temp;
 by fid date;
 flag=0;Multi=0;
 if first.fid then n=0;
 if first.date then n+1;
 if n=1 then flag=1;
 if flag=1 and a>b and not first.date then flag=0;
 if first.fid and b>1 then Multi=1;
 drop n a b ;
run;