Obsidian | Level 7

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Garnet | Level 18

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

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;
``````
Garnet | Level 18

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

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;
``````
Discussion stats