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,
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.