How to derive these column E and F(excel) in SAS?

[sas_newbie94]

Hi @Kurt_Bremser 

Thanks for the replies. 

Please find below information which you require:

data have;
infile datalines missover;
input accNo AMT Sequence Flag;
datalines;
1 111663 29
1 111760 30
1 111986 31 1
1 108676 32
1 108774 33
1 269278 34 1
1 269278 35
1 269004 36
1 265680 1
1 265120 2 1
1 264504 3
1 263420 4
2 194655 1 1
3 114645 1
3 114378 2 1
3 200000 3
3 15000 4
;
run;

options mprint;
data want (drop=start x);
    set have;
    retain start;

    /* New_Flag logic */
    if flag = 1
        then do;
            new_flag = 1;
            start = 1;
        end;
    else if sequence = 1 or accNo ne lag(accNo)
        then do;
            new_flag = .;
            start = 0;
        end;
    else if start = 1
        then new_flag = 1;

    x = new_flag;
run;

proc print; run;

[Kurt_Bremser]

Try this:

data have;
infile datalines missover;
input accNo AMT Sequence Flag;
datalines;
1 111663 29
1 111760 30
1 111986 31 1
1 108676 32
1 108774 33
1 269278 34 1
1 269278 35
1 269004 36
1 265680 1
1 265120 2 1
1 264504 3
1 263420 4
2 194655 1 1
3 114645 1
3 114378 2 1
3 200000 3
3 15000 4
;

data want;
set have;
by accno;
retain newflag newamt;
if first.accno or sequence = 1
then do;
  newflag = .;
  newamt = .;
end;
if flag = 1
then do;
  newflag = 1;
  newamt = amt;
end;
if newamt ne . then newamt = max(amt,newamt);
run;

proc print data=want noobs;
run;

Result:

accNo	AMT	Sequence	Flag	newflag	newamt
1	111663	29	.	.	.
1	111760	30	.	.	.
1	111986	31	1	1	111986
1	108676	32	.	1	111986
1	108774	33	.	1	111986
1	269278	34	1	1	269278
1	269278	35	.	1	269278
1	269004	36	.	1	269278
1	265680	1	.	.	.
1	265120	2	1	1	265120
1	264504	3	.	1	265120
1	263420	4	.	1	265120
2	194655	1	1	1	194655
3	114645	1	.	.	.
3	114378	2	1	1	114378
3	200000	3	.	1	200000
3	15000	4	.	1	200000

[sas_newbie94]

Dear @Kurt_Bremser. , thanks for the replies. However for the ones highlighted, it appears incorrect. It should show 114378 for subsequent rows as the flag of value 1 hits and subsequent rows should inherit that value until it either hits a new account or within the same account, a new sequence of 1.

Desired Output:

[sas_newbie94]

For further clarity here's a sample data which i have tweak the numbers abit:

data have;
infile datalines missover;
input accNo AMT Sequence Flag;
datalines;
1 111663 29
1 111760 30
1 111986 31 1
1 500000 32
1 500000 33
1 269278 34 1
1 500000 35
1 500000 36
1 265680 1
1 265120 2 1
1 500000 3
1 500000 4
2 194655 1 1
3 114645 1
3 114378 2 1
3 500000 3
3 500000 4
;

For the new amount indicator, I will only take the higher of the two between the first time it hits a value of 1 in the same account and the subsequent time it hits a value of 1 in the Flag column. If there's no 2nd hit of value of 1 in the same account, it will retain the value of 1 initially flagged.

Desired Output:

 

[Kurt_Bremser]

Just a slight expansion of the condition:

data want;
set have;
by accno;
retain newflag newamt;
if first.accno or sequence = 1
then do;
  newflag = .;
  newamt = .;
end;
if flag = 1
then do;
  newflag = 1;
  newamt = amt;
end;
if newamt ne . and flag = 1 then newamt = max(amt,newamt);
run;

[sas_newbie94]

Dear @Kurt_Bremser , thanks for staying throughout.

I have found a tiny loophole is that within the same account, if there's a trigger flag, it still did not take the higher of the two.

Please find below sample dataset and output:

data have;

infile datalines missover;

input accNo AMT Sequence Flag;

datalines;

1 111663 29

1 111760 30

1 111986 31 1

1 500000 32

1 500000 33

1 269278 34 1

1 500000 35

1 500000 36

1 100000 37

1 100000 38 1

1 100000 39

1 100000 40

1 100000 41

1 100000 42

1 265680 1

1 265120 2 1

1 500000 3

1 500000 4

2 194655 1 1

3 114645 1

3 114378 2 1

3 500000 3

3 500000 4

;

Output with your codes: For observation 10 to 14, we are comparing 100000 with 269278, we will be taking the higher of the two. Unsure why the MAX formula did not kick in for this scenario though. Appreciate your help on this once again! 

[Kurt_Bremser · Accepted solution]

I overlooked a detail in my code; the new condition needs to be implemented in another place:

data want;
set have;
by accno;
retain newflag newamt;
if first.accno or sequence = 1
then do;
  newflag = .;
  newamt = .;
end;
if flag = 1
then do;
  newflag = 1;
  newamt = max(amt,newamt);
end;
run;

[Tom]

It looks like you are tying to calculate a running MAX() of the AMT variable per ACCNO, but you have to wait to start until you see the race director drop the FLAG.

data want ;
  set have;
  by accno;
  if first.accno then call missing(start_counting,max_amt);
  start_counting=max(start_counting,flag);
  if start_counting then max_amt = max(amt,max_amt);
  retain start_counting max_amt;
run;

For tested code you need to post your example data as code instead of photographs.