@Lee_wan wrote:
Hi thanks for your response.
Because there are maybe multiple records with the same svstdtc\svstdtc but different visit existing in SV data.
Just like “
M01 01 2019-09-26 2019-09-26
M01 02 2019-09-26 2019-09-26
”
When I use proc sql,the records will be increase.
Then use a subselect to reduce the entries in sv:
data vs;
length
id $3
vstestcd $2
vsdt 4
;
format vsdt yymmddd10.;
input id vstestcd vsdt :yymmdd10.;
datalines;
M01 AA 2019-09-26
M01 BB 2019-09-26
M01 AA 2019-10-15
M01 BB 2019-10-15
M01 AA 2019-10-23
M02 AA 2019-09-12
M02 BB 2019-09-12
M02 AA 2019-09-23
M02 BB 2019-09-23
M02 AA 2019-09-30
;
data sv;
length
id $3
visit 3
svstdt
svendt
4
;
format
visit z2.
svstdt
svendt
yymmddd10.
;
input id visit (svstdt svendt) (:yymmdd10.);
datalines;
M01 01 2019-09-26 2019-09-26
M01 02 2019-09-26 2019-09-26
M01 02 2019-10-15 2019-10-15
M01 03 2019-10-19 2019-10-19
M01 04 2019-10-23 2019-10-23
M01 05 2019-11-03 2019-11-03
M02 01 2019-09-12 2019-09-12
M02 02 2019-09-23 2019-09-23
M02 03 2019-09-26 2019-09-26
M02 04 2019-09-30 2019-09-30
M02 05 2019-10-08 2019-10-08
;
proc sql;
create table want as
select
a.*,
vs.vstestcd,
vs.vsdt
from (
select
id,
max(visit) as visit format=z2.,
svstdt,
svendt
from sv
group by id, svstdt, svendt
having sv.visit = calculated visit
) a left join vs
on a.id = vs.id and a.svstdt <= vs.vsdt <= a.svendt;
quit;
proc print data=want noobs;
run;
(I added an additional observation in sv to create your "double" scenario, and changed visit to numeric, to allow use of the max() summary function)
Result:
id visit svstdt svendt vstestcd vsdt
M01 02 2019-10-15 2019-10-15 AA 2019-10-15
M01 04 2019-10-23 2019-10-23 AA 2019-10-23
M01 02 2019-09-26 2019-09-26 BB 2019-09-26
M01 02 2019-09-26 2019-09-26 AA 2019-09-26
M01 02 2019-10-15 2019-10-15 BB 2019-10-15
M01 03 2019-10-19 2019-10-19 .
M01 05 2019-11-03 2019-11-03 .
M02 01 2019-09-12 2019-09-12 BB 2019-09-12
M02 01 2019-09-12 2019-09-12 AA 2019-09-12
M02 04 2019-09-30 2019-09-30 AA 2019-09-30
M02 02 2019-09-23 2019-09-23 BB 2019-09-23
M02 02 2019-09-23 2019-09-23 AA 2019-09-23
M02 03 2019-09-26 2019-09-26 .
M02 05 2019-10-08 2019-10-08 .
As you can see, on 2019-09-26 only visit 02 is taken.
... View more