Dear,
Thank you very much for the code. The worked for me. But i need to add two more logic in my code to make efficient when new data added.
I need help in proc sql code below for id=7 and 8. These subjects have two or more 'NE' between two 'CR' and two 'PR'. I am attaching a document showing how the output look like in after proc sql step. For highlighted in yellow OBS, I need to apply "intck('day', a.rdate,a.dt)" if two or more "NE" between two 'CR' (for id=7). I need to apply "intck('day', a.dt, b.dt)" if two or 'NE' between two PR (for id=8). Please help. Thank you very much.
output needed:
for OBS=70 the 'nod' variable value should be 665.
for obs =73 the 'nod' variable value should be as shown in output(193)
data one;
input id rd $10. a $14-39 date $41-93;
datalines;
1 2016-01-01 PR CR CR CR CR CR CR CR CR 20803 20853 20915 20978 21041 21105 21188 21279 21357
2 2016-01-01 PR PR PR PR CR CR 21109 21171 21227 21290 21350 21433
3 2016-01-01 CR CR CR CR CR 21119 21187 21243 21312 21370
4 2016-01-01 CR CR PR 21272 21329 21384
5 2016-01-01 CR NE 21272 21329
6 2016-01-01 CR NE CR 21272 21329 21384
7 2016-01-01 CR NE NE CR 21119 21187 21243 21312
8 2016-01-01 PR NE NE PR 21119 21187 21243 21312
;
data two;
set one;
line = _n_;
length r $8;
do i = 1 to countw(a);
r = scan(a, i);
dt = input(scan(date, i), best.);
rdate=input(rd,yymmdd10.);
output;
end;
format dt rdate date9.;
keep id line r dt i rdate;
run;
proc sql;
create table four as
select
a.id, a.rdate,
a.r as a_1,
a.dt as dt_1,
b.r as a_2,
b.dt as dt_2,
case
when a_1 in ('CR' 'PR') and a_2 in ('CR' 'PR')then
intck('day', a.dt, b.dt)
when a_1 in ('CR' 'PR') and a_2 not in ('CR' 'PR') then intck('day', a.rdate,a.dt) end as nod
from two as a inner join two as b on a.id=b.id and a.i < b.i
where calculated nod ^= .
order by id, a.i, b.i;
quit;
... View more