DATA Step, Macro, Functions and more

Find changes between records and create an indicator for it

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Find changes between records and create an indicator for it

Hi all,

 

As I'm quite new to SAS I'm still learning some basic steps.

 

I have the following dataset:

 

contract_nr start_date end_date product_code

1392358 01-02-2017 31-07-2017 A
1392358 01-08-2017 30-11-2017 A
1392358 01-12-2017 28-02-2018 B
1392358 01-03-2018 30-04-2018 B

1847204 08-12-2017 31-12-2017 A

1847204 02-02-2018 31-03-2018 B

 

(I tried to make a example SAS code, but failed)

 

I want to find out when a product code has changed, but only if there are less than, say, 20 days between the start and end date. So, my want dataset looks like this:

 

contract_nr start_date end_date product_code indicator

1392358 01-02-2017 31-07-2017 A 0
1392358 01-08-2017 30-11-2017 A 0
1392358 01-12-2017 28-02-2018 B 1
1392358 01-03-2018 30-04-2018 B 1

1847204 08-12-2017 31-12-2017 A 0

1847204 02-02-2018 31-03-2018 B 0

 

By the way, the product code can only change from A to B. 

 

I prefer not to use loops, but I do think we can get around them. I hope someone can help me!


Accepted Solutions
Solution
‎04-17-2018 06:53 AM
Super User
Posts: 9,877

Re: Find changes between records and create an indicator for it

data have;
input contract_nr $ start_date :ddmmyy10. end_date :ddmmyy10.  product_code $;
cards;
1392358 01-02-2017 31-07-2017 A
1392358 01-08-2017 30-11-2017 A
1392358 01-12-2017 28-02-2018 B
1392358 01-03-2018 30-04-2018 B
1847204 08-12-2017 31-12-2017 A
1847204 02-02-2018 31-03-2018 B
;
run;

data want;
set have;
if start_date - lag(end_date) > 20 and product_code ne lag(product_code) and contract_nr = lag(contract_nr) then indicator = 1;
else indicator = 0;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
PROC Star
Posts: 1,564

Re: Find changes between records and create an indicator for it

You wrote "20 days between the start and end date. So, my want dataset looks like this:"

 

Can you mark this in the output to help me understand better?

Solution
‎04-17-2018 06:53 AM
Super User
Posts: 9,877

Re: Find changes between records and create an indicator for it

data have;
input contract_nr $ start_date :ddmmyy10. end_date :ddmmyy10.  product_code $;
cards;
1392358 01-02-2017 31-07-2017 A
1392358 01-08-2017 30-11-2017 A
1392358 01-12-2017 28-02-2018 B
1392358 01-03-2018 30-04-2018 B
1847204 08-12-2017 31-12-2017 A
1847204 02-02-2018 31-03-2018 B
;
run;

data want;
set have;
if start_date - lag(end_date) > 20 and product_code ne lag(product_code) and contract_nr = lag(contract_nr) then indicator = 1;
else indicator = 0;
run;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 9

Re: Find changes between records and create an indicator for it

Posted in reply to KurtBremser

Thanks! Never thought it would be this easy

Respected Advisor
Posts: 2,807

Re: Find changes between records and create an indicator for it

[ Edited ]

It seems like, on the one hand, you are comparing a record to the record immediately above it, but then you also have this 20 day restriction which is comparing two values on the same line (and not the record above it). It's not clear to me how these two restrictions work together. If I am understanding this properly (and I probably am not understanding it properly), the 20 day restriction would mean that the indicator variable would result in zeros for every row on the table.

--
Paige Miller
Occasional Contributor
Posts: 9

Re: Find changes between records and create an indicator for it

Posted in reply to PaigeMiller

I'm sorry for not being clear.

 

There has to be a 20-day restriction between records, not on the same line. So, I want to know the number of days between the last end date and the next start date. This number has to be smaller than 20.

 

So, if you look at my example dataset. The number of days between 30-11-2017 and 01-12-2017 is 0 (for 1392358). Therefore the change from A to B should get a 1.

 

For 1847204, there are 32 days between 31-12-2017 and 02-02-2018. Therefore, the indicator should be 0 for this change.

PROC Star
Posts: 1,564

Re: Find changes between records and create an indicator for it

Assuming i understand what you want:

 

data have;
input contract_nr $ start_date :ddmmyy10. end_date :ddmmyy10.  product_code $;
format start_date end_date  ddmmyy10.;
cards;
1392358 01-02-2017 31-07-2017 A
1392358 01-08-2017 30-11-2017 A
1392358 01-12-2017 28-02-2018 B
1392358 01-03-2018 30-04-2018 B
1847204 08-12-2017 31-12-2017 A
1847204 02-02-2018 31-03-2018 B
;
run;
data want;
set have;
by contract_nr;
if first.contract_nr then call missing(k);
retain k;
indicator= product_code='B'  and intck('day',k,start_date)<20 ;
k=end_date;
drop k;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 93 views
  • 1 like
  • 4 in conversation