Data check

[vandhan]

I have data like below.

id----Visit --------date------- response

01    Base   02APR2019      N

01    Vis 1   18APR2019      N

01    Vis 1   20APR2019      N 

01    Vis  2   04MAY2019      Y 

01    Vis  3   16MAY2019      N

Want :

1. if next record date difference date <=10 days then I want delete the records regardless of visit name.

2. I want per id until Y response. from Y I dont want records. In this I dont want Vis 3 record from above data.

Please help.

[Reeza]

Please show expected output from this data.

[vandhan]

id----Visit --------date------- response

01    Base   02APR2019      N

01    Vis 1   18APR2019      N

01    Vis 1   20APR2019      N 

01    Vis  2   04MAY2019      Y 

01    Vis  3   16MAY2019      N

3rd and 5th records I want to remove.

3rd - since difference with previous visit is just 2 days. I want more than 10 days gap between visits.

5th - Response is N after Y response. I want to remove all records which occured after Y response for particular subject.

[PeterClemmensen]

So in total, which records should be deleted here?

[vandhan]

id----Visit --------date------- response

01    Base   02APR2019      N

01    Vis 1   18APR2019      N

01    Vis 1   20APR2019      N 

01    Vis  2   04MAY2019      Y 

01    Vis  3   16MAY2019      N

3rd and 5th records I want to remove.

3rd - since difference with previous visit is just 2 days. I want more than 10 days gap between visits.

5th - Response is N after Y response. I want to remove all records which occured after Y response for particular subject.

[Reeza]

data want;
set have;

retain response_flag;

prev_date = lag(date);
response_flag = 0;

if date - prev_date < 10 then delete;

if response_flag = 1 then delete;

if response = 'Y' and response_flag = 0 then response_flag=1;

run;

---

@vandhan wrote:

id----Visit --------date------- response

01    Base   02APR2019      N

01    Vis 1   18APR2019      N

01    Vis 1   20APR2019      N 

01    Vis  2   04MAY2019      Y 

01    Vis  3   16MAY2019      N

3rd and 5th records I want to remove.

3rd - since difference with previous visit is just 2 days. I want more than 10 days gap between visits.

5th - Response is N after Y response. I want to remove all records which occured after Y response for particular subject.

---

[Tom]

what if the RESPONSE='Y' record appears on an observation that your rule says to delete?

[Reeza]

And you should probably add more than one ID since your real data will likely have that unless you're sure you're comfortable generalizing the code.

[novinosrin]

Hello @vandhan 

data have;
input id Visit & $10.  date :date9. response $;
format date date9.;
cards;
01    Base   02APR2019      N
01    Vis 1   18APR2019      N
01    Vis 1   20APR2019      N 
01    Vis 2   04MAY2019      Y 
01    Vis 3   16MAY2019      N
;

data want ;
do _n_=1 by 1 until(last.id);
 set have;
 by id;
 if (dif(date)>10 or _n_=1) and not _k then output;
 if response='Y' then _k=1;
end;
drop _:;
run;

Notes:

(dif(date)>10 or _n_=1)  can also be written as (dif(date)>10 or first.id)

[vandhan]

Thanks for your reply. I have I more condition to be added.

If Visit is ‘Unsch’ then that record should be deleted if Days gap is less than 10. Hence here 4rd and 6th record should be deleted

Can you add the condition also please. I have added one more below for ‘Unsch’ visit

data have;
input id Visit & $10. date :date9. response $;
format date date9.;
cards;
01 Base 02APR2019 N
01 Vis 1 18APR2019 N
01 Vis 2 20APR2019 N
01 Unsch 22APR2019 Y
01 Vis 2 04MAY2019 Y
01 Vis 3 16MAY2019 N
;

[Reeza]

Have you tried modifying it yourself so far? If so, please post what you've tried. It's a bad idea to run code you don't understand...

[novinosrin]

"It's a bad idea to run code you don't understand..."

@Reeza  That's Bingo!

Reminds me of  my dire situation back in Sep-oct 2012, hmm  My very first basic exposure to SAS and didn't even understand what's DATA and SET statement. Jeez!, Believe it or not, was sitting at work. Used my desk phone to call my mom. Give her the HAVE and WANT. Mom used som crazy proc sql to avoid hoops of arrays and loops but compelled to use loops at times. Boss, holding me on the fire. Didn't understand the solutions nor the HAVE/WANT and finally  However , I quit within 3 months and fell into depression. 

[Reeza]

Your mom is a SAS programmer? You're a second generation SAS programmer! That's kinda cool. I don't know very many people outside of work who even know what SAS is - first in my family to get a post secondary degree type background.

Yeah, a good boss gives you space to learn while still holding your feet to the fire 😛

[novinosrin]

"Your mom is a SAS programmer?" - No. She was forced to learn for my sake as I was struggling. So what i learned in 6 years, she did in half the time. We started together. lol

[Reeza]

lol, that's interesting. Still good that you had someone to rely on 🙂