I have been thinking using Proc Sql self-join on the question below for many days. Somehow, i just cannot get the exact results. I really appreciate anyone can help me to resolve the puzzle. The general logic is "There are more than 2 test use different ID within 2 days from the same account and the total amount by these tests is above 4000 dollars. If so, the flag is 1, else the flag is 0". I want to produce the last column flag.
data temp;
input obs account $ date : mmddyy10. time : time9. test id amount hit;
format date mmddyy10. time time9.;
datalines;
1 387000 6/10/2010 12:00:00 1 13.15 2000 1
2 387000 6/9/2010 12:00:00 1 13.15 3000 0
3 387000 6/8/2010 12:00:00 1 13.14 2000 0
4 387000 6/6/2010 11:00:00 1 13.15 2000 1
5 387000 6/5/2010 10:00:00 1 13.14 2000 0
6 387000 6/4/2010 9:00:00 1 13.13 1000 0
7 387000 6/4/2010 8:00:00 1 13.13 500 0
8 386000 5/9/2010 10:00:00 1 12.12 5000 0
9 386000 5/2/2010 9:00:00 1 12.14 2000 0
10 386000 5/1/2010 8:00:00 1 12.13 2000 0
11 385000 3/3/2010 10:00:00 1 11.13 5000 1
12 385000 3/1/2010 9:00:00 1 11.12 3000 0
13 385000 3/1/2010 8:00:00 1 11.11 3000 0
;
run;
For example:
In obs 1, account 387000, within 2 days, (06/10 /10 -06/08/10), there are three test adopted different ID (13.15 and 13.14), and the total money is more than 4000 (2000+3000+2000), so the flag is 1
In obs 2, account 387000, within 2 days, (06/9/10 -06/07/10), there are only two test adopted different ID, even though the money is above $4000. The flag is 0
in Obs 3, account 387000, within 2 days, (06/8/10 -06/06/10), there are only two test adopted different ID, and the money is not more than $4000. The flag is 0
in Obs 4, account 387000, within 2 days, (06/6/10 -06/04/10), there are four usage adopted different ID (13.15, 13.14, and13.13), and the money is more than $4000 (it is 5500). The flag is 1
The flag has to satisfy the following points:
1) In the same account
2) within 2 days
3) more than 2 test (each observation is one test)
4) the sum of the amount by these test is above $4000
5) used different ID.
Thank you very much for your help!
... View more