Hi All,
Would require assistance to achieve the following new amount value to be inputted based on a flag and the specified amount tied to it.
Please find screenshot for reference. For each account number, I would like to RETAIN the first amount at each occurrence of flag and affect subsequent records below. Once there's a new amount, it will take in the new amount and bring it down to the subsequent rows below. This behavior applies to the same account number until it reaches a new account number.
data example; input accno flag amount ; datalines; 100 . . 100 . . 100 . . 100 . . 100 1 1000 100 1 . 100 1 . 100 1 . 100 1 3000 100 1 .
200 . . 200 . . 200 . . 200 . . 200 . . 200 1 500 200 1 . 200 1 . 200 1 700 200 1 . ;
run;
... View more
Hi both @PaigeMiller @Ksharp , Thanks for the replies. Both codes worked! However I have one more additional criteria that I need to look out for when recalculating the sequence. If an account number does not have an AMT or AMT that is showing 0, we do not use back the old sequence column despite it being the FIRST.ACCNO. Pls refer to accno 900 for this scenario. Instead, at the point where there's an AMT we will have to recalculate the new_sequence and start from 1. Initially I thought I can omit AMT = 0 entirely from the data set, but take note that at subsequent rows if there's 0 AMT, we still retain the new_sequence count from above. Pls refer to highlighted in red text for 0 Amt for accno 900. It will still continue counting. ONLY at the starting point for each Accno, we need to check for the AMT value whether it's showing 0. If it's 0, we need to start counting at the instance when there's a AMT value. For accno 991, we can use back the old sequence since it's the FIRST.ACCNO and there's an $ AMT to that row, so we can use back the old sequence and start flagging from there. Prior checks for "Flag to reset" condition still exist. data example;
input accno old_sequence flag_to_reset AMT ;
datalines;
900 40 . 0
900 41 . 0
900 42 . 100
900 43 . 100
900 44 1 100
900 45 . 100
900 46 . 100
900 47 . 0
900 48 . 0
991 20 . 100
991 21 . 100
991 22 1 100
991 23 . 100
991 24 . 100
991 25 . 100
991 26 . 100
;
run;
... View more
Hi All, Would require assistance to achieve the following loop to create a NEW sequence column based on a flag. Please find screenshot for reference. For each account number, I would like to RETAIN the first sequence. Meaning for accno 900, first sequence to retain is 40. For acc 991, first sequence to retain is 20. From there, it will add an incremental of +1 all the way until it hits a flag for reset with the value of 1. If it hits a value of 1 in the flag, it will reset the sequence and it will start from 1 and then add an incremental of +1 subsequently. data example;
input accno old_sequence flag_to_reset new_sequence;
datalines;
900 40 40
900 41 41
900 42 42
900 43 43
900 44 1 1
900 45 2
900 46 3
900 47 4
900 48 5
991 20 20
991 21 21
991 22 1 1
991 23 2
991 24 3
991 25 4
991 26 5
;
run;
... View more
Data is currently stored as date format, YYYYMM. However I would like to create a text column with just year and month. Meaning to say YYMM, I know this is not a good way to store data however I require it in that format. Meaning to say for YYYYMM = 201906, desired output = 1906. YYYYMM = 202006, desired output = 2006. YYYYMM = 200606, desired output = 0606. data have;
infile datalines missover;
input yymm :yymmn6.;
format yymm yymmn6.;
datalines;
201904
201807
201912
202005
;
... View more
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!
... View more
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:
... View more
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:
... View more
Hi anyone has any idea how to create additional rows in SAS dataset based on latest observation? Here's my dataset: data have; infile datalines missover; input accNo AMT yymm sequence; datalines; 1 111663 1904 40 2 120000 1807 50 3 111986 1912 32 4 108676 2005 20 ; run; Table: What I am trying to achieve is that for every account number if you notice the last record for yymm column is 1904 for accno 1. The sequence is 40. I would like to add additional rows with the same values but yymm will move by a month accordingly till date. The sequence will also move and + 1 accordingly. Please find desired output below. Take note that for yymm ending with 12, the yymm for example is 1912, the next one will be 2001. Desired Output:
... View more
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:
... View more
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!
... View more
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.
... View more
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.
... View more
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.
... View more
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),""))
... View more
