Create a variable if a positive value is followed by a negative value for some another variable?

Shradha1 — Tue, 16 Mar 2021 12:22:46 GMT

I have data on spends for a group of people, begining from their very first spend. The spends amount is somewhere positive and somewhere negative (denoting Credit or Debit).I have to pick all those transactions where we have a negative spend happening within 7 days of any positive spend. My data is like this:

NAME Date Amount
A 3-Jan 400
A 4-Jan 2000
A 5-Jan 3
A 6-Jan 23
A 7-Jan -2400
A 8-Jan -32
A 9-Jan 400
A 10-Jan -340
B 2-May 987
B 3-May -453
B 4-May 544
B 5-May 765
B 6-May -456
C 5-Apr 888
C 6-Apr 1000
C 7-Apr 250
C 8-Apr 450
C 9-Apr 654
C 10-Apr 1000
C 11-Apr 240

The lookup or iteration will reset each time we have a positive spend. For example in row no. 8 for A, we have a positive spend again after negative spend, so we will now again look within 7 days after this spend, and if we encounter any negative spend, the flag will be created. The resultant data will look like this:

NAME	Date	Amount	Flag
A	3-Jan	400	1
A	4-Jan	2000	1
A	5-Jan	3	1
A	6-Jan	23	1
A	7-Jan	-2400	1
A	8-Jan	-32	1
A	9-Jan	400	1
A	10-Jan	-340	1
B	2-May	987	1
B	3-May	-453	1
B	4-May	544	1
B	5-May	765	1
B	6-May	-456	1
C	5-Apr	888	0
C	6-Apr	1000	0
C	7-Apr	250	0
C	8-Apr	450	0
C	9-Apr	654	0
C	10-Apr	1000	0
C	11-Apr	240	0

How can I acheive this? It would be really helpful if someone would help on this. Thanks!

Re: Create a variable if a positive value is followed by a negative value for some another variable?

Shmuel — Tue, 16 Mar 2021 12:40:25 GMT

Next code is tested:

%let year = 2020;
data have;
  infile cards;
  input NAME $ Datex $ Amount;
  date = input(compress(datex||"&year",'-'),date9.);
  format date date9.;
cards;
A 3-Jan 400
A 4-Jan 2000
A 5-Jan 3
A 6-Jan 23
A 7-Jan -2400
A 8-Jan -32
A 9-Jan 400
A 10-Jan -340
B 2-May 987
B 3-May -453
B 4-May 544
B 5-May 765
B 6-May -456
C 5-Apr 888
C 6-Apr 1000
C 7-Apr 250
C 8-Apr 450
C 9-Apr 654
C 10-Apr 1000
C 11-Apr 240
; run;

data temp;
 set have;
  by name;
     retain flag;
     if first.name then flag = 0;
     if amount < 0 and dif(date) le 7
     then  flag = 1;
     if last.name then output;
run;

proc sql;
  create table want
  as select a.*, b.flag
  from have as a left join temp as b 
  on a.name = b.name;
quit;

topic Create a variable if a positive value is followed by a negative value for some another variable? in SAS Programming

Create a variable if a positive value is followed by a negative value for some another variable?

Re: Create a variable if a positive value is followed by a negative value for some another variable?