How to create a variable based on the values of other variables?(In continuation from my last post)

[Shradha1]

This is in continuation from my last question (which was solved).

The last question was:

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

I have been able to create this flag with the help of @Shmuel . Now my requirement is that I want to take all the sum of positive amounts and the sum of negative amount wherever the negative amount has appeared within 7 days of the positive amount, i.e., where the flag is 1. So against every negative amount with flag=1, 2 variables will be populated, sum of positive amounts(within the last 7 days) and absolute sum of debit amounts. The data will look like this:

NAME	Date	Amount	Flag	Positive_sum	Negative_sum
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	2426	2400
A	8-Jan	-32	1	2426	2432
A	9-Jan	400	1
A	10-Jan	-340	1	2826	2772
B	2-May	987	1
B	3-May	-453	1	987	453
B	4-May	544	1
B	5-May	765	1
B	6-May	-456	1	2296	909
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

Please help me on this. Thanks a lot!

[Shmuel · Accepted solution]

Assuming data is already sorted by NAME and DATE you can use next tested code:

data want1;
 set want;
   by name;
        retain sum_p sum_n sw;
        drop   sum_p sum_n sw;
        if first.name 
		then do;
           sum_p = 0;
           sum_n = 0;
		   sw = 0;
        end;
        if flag = 1 then do;
           if amount > 0 then sum_p + amount;
           else do;
             sum_n + amount;
		     positive_sum = sum_p;
		     negative_sum = abs(sum_n);
		     sw = 1;
		   end;
	    end;
		/* else flag=0 do nothing */
run;

[Shradha1]

Thanks a lot! This worked