topic Re: Data check in SAS Programming

Data check

vandhan — Wed, 14 Aug 2019 15:10:15 GMT

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.

Re: Data check

Reeza — Wed, 14 Aug 2019 15:11:06 GMT

Please show expected output from this data.

Re: Data check

PeterClemmensen — Wed, 14 Aug 2019 15:20:36 GMT

So in total, which records should be deleted here?

Re: Data check

Reeza — Wed, 14 Aug 2019 15:22:47 GMT

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.

Re: Data check

vandhan — Wed, 14 Aug 2019 15:25:24 GMT

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.

Re: Data check

vandhan — Wed, 14 Aug 2019 15:26:50 GMT

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.

Re: Data check

Reeza — Wed, 14 Aug 2019 15:52:11 GMT

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.

Re: Data check

novinosrin — Wed, 14 Aug 2019 16:21:29 GMT

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)

Re: Data check

Tom — Wed, 14 Aug 2019 20:41:54 GMT

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

Re: Data check

vandhan — Thu, 15 Aug 2019 17:19:22 GMT

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
;

Re: Data check

Reeza — Thu, 15 Aug 2019 17:20:44 GMT

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...

Re: Data check

novinosrin — Thu, 15 Aug 2019 17:35:55 GMT

"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.

Re: Data check

novinosrin — Thu, 15 Aug 2019 17:53:46 GMT

Hello @vandhan Please post the below

1. Sample of HAVE (which you did)

2. Sample of WANT(expected output for the sample HAVE)

3. Business logic to be coded.

Figures are much more helpful. Also, take your time, get the requirements in full and we shall get it done in one shot. Cheers!

Re: Data check

Reeza — Thu, 15 Aug 2019 18:00:42 GMT

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 😛

Re: Data check

novinosrin — Thu, 15 Aug 2019 18:04:58 GMT

"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

Re: Data check

Reeza — Thu, 15 Aug 2019 18:15:07 GMT

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