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 |
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
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
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.