I am trying to create a new variable called "stroke visit" using the information below. I have the date of stroke and visit numbers available. The stroke visit would be closest to the date of stroke (preferably before the latest visit) e.g. for ID 2 the visitnum should be "3".
id stroke dateofstrk visitdate visitnum
1 0 . 11/1/2022 1
1 0 . 12/1/2022 2
1 0 . 1/1/2023 3
2 1 12/25/2022 11/1/2022 1
2 1 12/25/2022 12/1/2022 2
2 1 12/25/2022 1/1/2023 3
3 1 11/13/2022 11/1/2022 1
3 1 11/13/2022 12/1/2022 2
3 1 11/13/2022 11/1/2023 3
The date of the first visit occuring at or after the date of stroke can be obtained as:
data have;
input id stroke (dateOfStrk visitDate) (:mmddyy.) visitNum;
format dateofStrk visitDate yymmdd10.;
datalines;
1 0 . 11/1/2022 1
1 0 . 12/1/2022 2
1 0 . 1/1/2023 3
2 1 12/25/2022 11/1/2022 1
2 1 12/25/2022 12/1/2022 2
2 1 12/25/2022 1/1/2023 3
3 1 11/13/2022 11/1/2022 1
3 1 11/13/2022 12/1/2022 2
3 1 11/13/2022 11/1/2023 3
;
proc sql;
/* create table want as */
select
a.*, b.strkVisit format=yymmdd10.
from
have as a left join
(select id, min(visitDate) as strkVisit
from have where stroke and visitDate>=dateOfStrk
group by id) as b
on a.id=b.id
order by id, visitNum;
quit;
The date of the first visit occuring at or after the date of stroke can be obtained as:
data have;
input id stroke (dateOfStrk visitDate) (:mmddyy.) visitNum;
format dateofStrk visitDate yymmdd10.;
datalines;
1 0 . 11/1/2022 1
1 0 . 12/1/2022 2
1 0 . 1/1/2023 3
2 1 12/25/2022 11/1/2022 1
2 1 12/25/2022 12/1/2022 2
2 1 12/25/2022 1/1/2023 3
3 1 11/13/2022 11/1/2022 1
3 1 11/13/2022 12/1/2022 2
3 1 11/13/2022 11/1/2023 3
;
proc sql;
/* create table want as */
select
a.*, b.strkVisit format=yymmdd10.
from
have as a left join
(select id, min(visitDate) as strkVisit
from have where stroke and visitDate>=dateOfStrk
group by id) as b
on a.id=b.id
order by id, visitNum;
quit;
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.