BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
JLang055
Fluorite | Level 6

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.

 

FIDDatediagnosisflag (want)Multi (want)
12016-05-05anxiety10
12016-05-05anxiety00
12016-07-13ADHD00
12016-07-18Conduct00
22017-05-29conduct10
32017-08-16anxiety10
32017-10-18adhd00
42017-11-16conduct11
42017-11-16adhd10
42017-12-10anxiety00
1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

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
Super User
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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 937 views
  • 0 likes
  • 3 in conversation