topic Re: How to derive these column E and F(excel) in SAS? in SAS Enterprise Guide

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

sas_newbie94 — Mon, 05 Oct 2020 13:11:56 GMT

Hello i would like to derive column E and F in SAS. I have done it via Excel and this is as follows.

Column E: =IF(D2=1,1,IF(OR(C2=1,A2<>A1),"",E1))

Column F: =IF(AND(E1="",E2=1),B2,IF(AND(E2=1,E1=1),IF(B2>F1,B2,F1),""))

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

PaigeMiller — Mon, 05 Oct 2020 14:57:56 GMT

Speaking as someone who is not particularly good at Excel, I can't really interpret your Excel code, but ... YOU can! Write down your code in plain English as IF THEN statements, and show us those. From there, show your plain English to us. It should be relatively easy to translate plain English IF THEN statements into SAS IF THEN statements.

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

mklangley — Mon, 05 Oct 2020 15:36:52 GMT

Like @PaigeMiller suggested, converting your Excel formulas to plain English is a good first step. As another approach, I attempted to convert your formulas to pseudo-code. You can revise it if there are any errors.

if D2 = 1
then column_E = "1"

else if C2 = 1 or A2 ne A1

then column_E = " "

else column_E = E1

if (E1 = " " and E2 = 1)

then column_F = B2

else if (E2 = 1 and E1 = 1)

then do

if B2 > F1 then column_F = B2

else column_F = F1

end

else column_F = " "

(As a side note, make sure the formulas correspond to the data you provided. The formulas you included reference cells in row 1, which contains the headers (e.g. E1 is NEW FLAG). I don't think you intended those to be part of it.)

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

sas_newbie94 — Mon, 05 Oct 2020 15:44:07 GMT

Ah okay! So for Column E, the new flag column is derived basing on Column D the flag column. As long as there's a flag indicator where Column D hits 1, it will carry forward to subsequent rows in Column E until the point where Column C becomes 1 or when Column A is a new account number. It will loop and check for column D whether there's a flag for 1 and then once hit, it will add 1 to that row and the subsequent rows in Column E. I tried looking up and using the LAG function but it does not seems to work in this scenario.

For Column F, it's slightly more complicated where at the very first instance when Column E (derived above) has a flag 1, we will take that row $ Amt in Column B. For the subsequent rows, we are required to compare the newly derived amount in Column F with Column B, the higher of the two prevails and will be the new value in Column F which can be seen in the image uploaded.

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

sas_newbie94 — Mon, 05 Oct 2020 15:47:55 GMT

Hi thanks for the replies guys! Really appreciate it.

For SAS, unfortunately there's no cell reference like what I can do in Excel. Not sure if you have any idea how we can compare a single cell record in SAS with the subsequent records? I have tried using LAG in SAS but it's only able to compare previous record for Column B. For my case i will need a dynamic LAG to compare with my newly derived values in Column F against Column B row value.

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

Kurt_Bremser — Mon, 05 Oct 2020 15:56:26 GMT

You do have column names: accno, amt, sequence and flag. Use these, and add "from previous row" or "from first row in group" where adequate.

You can also describe your algorithm this way:

"in the first row of each accno I want to do X, in the next row I want to do Y, in subsequent rows I want to do Z".

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

ballardw — Mon, 05 Oct 2020 15:57:51 GMT

@sas_newbie94 wrote:

Hi thanks for the replies guys! Really appreciate it.

For SAS, unfortunately there's no cell reference like what I can do in Excel. Not sure if you have any idea how we can compare a single cell record in SAS with the subsequent records? I have tried using LAG in SAS but it's only able to compare previous record for Column B. For my case i will need a dynamic LAG to compare with my newly derived values in Column F against Column B row value.

RETAIN would likely be a better approach. That makes a variable whose values are maintained across iterations of the data step. You have to set the value for the retained value, likely copying an existing variable to the retained one as needed.

A not particularly useful example of the syntax where the Retain statement sets an initial value:

data work.class;
   set sashelp.class;
   retain beforejane 'Yes';
   if name='Jane' then beforejane='No';
run;

You should specify a length for retained character variables before the retain statement if you do not initialize them, or the initial value is shorter than some expected values that need to be assigned.

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

sas_newbie94 — Mon, 05 Oct 2020 16:20:33 GMT

Thanks for the tips!

For the first derived column, [NEW FLAG; Column E]:

In the first row of each account number (Column A), if there's no flag indication with the value 1 (Column D), my newly derived value (Column E) will be a blank. If an account number has an indication of value 1 (Column D). my new Flag column will inherit the value of 1 (Column E). This value will be inherited to subsequent rows with the value of 1 until there's a new account number (it will stop inheriting the value of 1 and check for the new account number if there's an indication of value 1 (Column D) again, in a way it will be 'resetted') OR if it's the same account number, the Sequence column (Column C) indicates a 1, this will also 'reset' the check and stop inheriting the value of 1 until there's a flag indication with the value of 1 (Column D) again.

For the second derived column, [NEW $ AMT; Column F]:

This part it will tend to be slightly trickier. In the first row where it's either a new accno OR same accno but sequence of 1, where the NEW FLAG Column E has a value of 1, we will take the first $ Amt and placed it under NEW $AMT which can be seen highlighted in yellow. For subsequent rows, the newly derived value in Column F we will have to compare with Column B and take the higher of the two which can be seen in the image attached.

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

TomKari — Mon, 05 Oct 2020 16:31:52 GMT

I think this will get you started. Note that the important part is the "retain" statement; it allows carrying a result forward. See if this is going in the direction you want, and we can keep working on it.

Tom

data Have;
	input AccNo AMT Sequence FLAG;
	cards;
1 111663 29 .
1 111663 30 .
1 111663 31 1
1 111663 32 .
1 111663 1 .
1 111663 2 1
2 111663 1 1
3 111663 1 .
3 111663 2 1
run;

data Want;
	set Have;
	retain OldAcc . Switch 0 NEWFLAG . NEWAMT .;

	if AccNo > OldAcc | Sequence = 1 then
		Switch = 0;

	if FLAG = 1 then
		Switch = 1;

	if Switch = 1 then
		do;
			NEWFLAG = 1;
			NEWAMT = AMT;
		end;
	else
		do;
			NEWFLAG = .;
			NEWAMT = .;
		end;

	OldAcc = AccNo;
run;

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

mklangley — Mon, 05 Oct 2020 16:36:04 GMT

@sas_newbie94 Give this a try:

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 108348 34  
    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 y);
    set have;
    retain start y;

    /* 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;
    else new_flag = lag(new_flag);

    x = new_flag;

    /* New_Amt logic */
    if x = . and new_flag = 1
        then new_amt = amt;
    else if (new_flag = 1 and x = 1)
        then new_amt = max(amt, y);
    else new_amt = .;

    y = new_amt;
run;

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

sas_newbie94 — Mon, 05 Oct 2020 17:03:16 GMT

Thanks @mklangley for your help! It's really close to getting the desired output! However apologies i may have flagged incorrectly for one of the rows for New $ Amt (Column F) which I have corrected the values and highlighted in green. Since it's a new account number, it should take the $ Amt at Column B as the NEW $ Amt and not the prior $ Amt from Account 1. Thankful for your help!

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

mklangley — Mon, 05 Oct 2020 17:23:59 GMT

Okay, how about this?

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

    /* 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;

    /* New_Amt logic */
    if x = . and new_flag = 1
        then new_amt = amt;
    else if (new_flag = 1 and x = 1 and accNo = lag(accNo))
        then new_amt = max(amt, y);
    else if (new_flag = 1 and x = 1 and accNo ne lag(accNo))
        then new_amt = amt;
    else new_amt = .;

    y = new_amt;
run;

proc print; run;

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

Tom — Mon, 05 Oct 2020 17:34:50 GMT

I don't see any need to look ahead in the photo you just posted.

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

Tom — Mon, 05 Oct 2020 17:51:41 GMT

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.

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

sas_newbie94 — Tue, 06 Oct 2020 09:52:55 GMT

Dear @mklangley ,

Thanks for your prompt replies and speedy updates. Your code is really good and I appreciate your help. However, I realised that I have another scenario where there's another logic to deriving new $ AMT column F. I would like to seek your help once again.

For Column F, new $ Amt, at the first time where Column D hits a value of 1, it will take the $ AMT for that row and inherit across subsequent rows until it hits another value of 1 in column D flag, that's where we will compare the two $ AMT and take the higher of the two. Take note that the new $ AMT needs to only be included for new flag = 1 and do the checks accordingly.

Desired Output as follows:

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

Kurt_Bremser — Tue, 06 Oct 2020 10:14:19 GMT

Please post your data in a data step with datalines, as @mklangley has already shown. We cannot test SAS code against pictures. Data steps with datalines are not rocket science, but a very useful skill, and help greatly in illustrating issues.

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

sas_newbie94 — Tue, 06 Oct 2020 10:31:21 GMT

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;

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

Kurt_Bremser — Tue, 06 Oct 2020 10:47:56 GMT

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

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

sas_newbie94 — Tue, 06 Oct 2020 11:07:29 GMT

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:

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

sas_newbie94 — Tue, 06 Oct 2020 11:15:33 GMT

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: