Hi Experts,
I have 8 numeric date columns such as visdt1 visdt2 visdt3 visdt4 visdt5 visdt6 visdt7 visdt8 for 10000 observations. I would like to pick the date that comes last for each observations as it is missing at different visdt vars at different time point.
Though using coalesce function and putting variables in reverse order works, but is there any other way of doing it, may be if there is a function like reverse() available for numeric variables if anyone know of, OR any others ideas please do post.
any other effeicient methods how to get the final visdt var for each 10000 observations?
the data structure looks like
id visdt1 visdt2 visdt3 visdt4 visdt5 visdt6 visdt7 visdt8
001 2014-07-31 2014-08-18 2014-08-05 2014-08-19 . .
002 2014-09-25 2014-10-09 . 2014-09-25 2014-10-09 2015-01-14 . .
003
004
Assuming visit dates are incremental (visdt1 < visdt2 < etc.), you can do this:
data want;
set have;
array _visdt(*) visdt1-visdt8;
format lastvisdt date9.;
lastvisdt = max(of _visdt(*));
run;
Otherwise, you can pick the last non missing observation:
data want;
set have;
array _visdt(*) visdt1-visdt4;
format lastvisdt date9.;
do i=1 to dim(_visdt);
if _visdt(i) ne . then lastvisdt = _visdt(i);
end;
drop i;
run;
Best,
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 16. 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.