About newboy1218

newboy1218 · ‎11-30-2022

Hi, I have a table contains customer ID, account #s, each month's balance (OS bal) of the accounts. Each customer can have multiple accounts in the dataset. What I want is to calculate on each customer, the total outstanding balance from all accounts currently on file when a new account is open (let's call it 'Total_Customer_OSBal'). For example, a customer has 3 accounts, one opened in Jan 2022, one opened in Mar 2022, and the last one opened in Jun 2022. The 'Total_Customer_OSBal' for the 1st account when it opened in Jan 2022 is $0. The 'Total_Customer_OSBal' for the 2nd account when it opened in Mar 2022 is the OS balance from the 1st account in Mar 2022. The 'Total_Customer_OSBal' for the 3rd account when it opened in Jun 2022 is the OS balance from the 1st account in Jun 2022 and 2nd account in Jun 2022. Is there an efficient way to do this? Here are the example chart for illustration: HAVE Customer_ID Account_num Date_Open Snap_Date Balance 1 1A MAR2022 MAR2022 500 1 1A MAR2022 APR2022 450 1 1A MAR2022 MAY2022 400 1 1B MAY2022 MAY2022 600 2 2A JAN2022 JAN2022 100 2 2A JAN2022 FEB2022 90 2 2A JAN2022 MAR2022 80 2 2A JAN2022 APR2022 70 2 2A JAN2022 MAY2022 60 2 2B JAN2022 JAN2022 210 2 2B JAN2022 FEB2022 190 2 2B JAN2022 MAR2022 170 2 2B JAN2022 APR2022 150 2 2B JAN2022 MAY2022 130 2 2C MAY2022 MAY2022 700 WANT Customer_ID Account_num Date_Open Snap_Date Balance Total_Customer_OSBal 1 1A MAR2022 MAR2022 500 0 1 1A MAR2022 APR2022 450 . 1 1A MAR2022 MAY2022 400 . 1 1B MAY2022 MAY2022 600 400 2 2A JAN2022 JAN2022 100 . 2 2A JAN2022 FEB2022 90 . 2 2A JAN2022 MAR2022 80 . 2 2A JAN2022 APR2022 70 . 2 2A JAN2022 MAY2022 60 . 2 2B JAN2022 JAN2022 210 100 2 2B JAN2022 FEB2022 190 . 2 2B JAN2022 MAR2022 170 . 2 2B JAN2022 APR2022 150 . 2 2B JAN2022 MAY2022 130 . 2 2C MAY2022 MAY2022 700 190 (60+130)

newboy1218 · ‎10-18-2022

yeah and I also tried using the FINANCE PMT function.. when I do PMT4 = finance('pmt', 0.008325, 24, 1750.37, -437.5925); it will give me -64.21491967, but if I do: PMT4 = finance('pmt', 0.008325, 24, 1750.37, 437.5925); it will give me -97.31031261.. Does anyone know why I need to put a negative sign in front of the FV when I am using Excel or FINANCE PMT functions?

newboy1218 · ‎10-18-2022

Hi, I used the SAS FMT function and Excel PMT function with the same inputs but they gave me different output.. Why is that? SAS: data aa; PMT = round(pmt(0.008325, 24, 1750.37, 437.5925), 0.01); run; Output: 64.21 Excel: A B 1 pv 1750.37 2 fv 437.5925 3 amortization 24 4 interest 0.0999 Formula: =PMT(B4/12,B3,B1,B2) Output: ($97.31)

newboy1218 · ‎06-17-2022

Oh nvm, I found it: https://financeformulas.net/Loan_Balloon_Balance.html

newboy1218 · ‎06-17-2022

Thank you for the response. I saw that doc as well, but in there, there is no formula.. Do you know the formula?

newboy1218 · ‎06-17-2022

Hi, does anyone know the formula behind the payment function in SAS? I need to replicate this somewhere else with future value being none 0. Thank you! PMT(rate, number-of-periods, principal-amount[, future-amount][, type])

newboy1218 · ‎04-26-2022

Hi, I have a dataset contain the age group and income info. I want to create a dataset from it, counting the cumulative income level by age group. For example, for each age group, I want to know how many people have income <= 1000, <= 2000, ... etc. I tried using proc sql - group by function (on age group) along with sum - case when on income, but I got all the income level as column instead of rows. Is there an efficient way to do this? HAVE: ID Age_Group Income 1 21-30 1000 2 51-60 1200 3 31-40 1400 4 51-60 2020 5 21-30 3000 6 51-60 3000 WANT: Age_Group Income Cumulative_count 21-30 1000 1 21-30 2000 1 21-30 3000 2 31-40 1000 0 31-40 2000 1 31-40 3000 1 51-60 1000 0 51-60 2000 2 51-60 3000 3

newboy1218 · ‎04-25-2022

Hi, I am trying to calculate the % of A debt amount within the sum of A + B debt amount, and then I want to filter out % of A debt amount is between 90 - 100% (excluding 90% and 100%). However, in my result, it return answer where a_debt_pcent = 100.. Do you know why even though I excluded 100%? Thanks. data aa; input a_debt_amt b_debt_amt; datalines; 10701.54 0 84256.96 0 21462.54 0 240 20 23876.83 0 95 5 396 184 982 3874 0 9128 ; run; data bb; set aa; a_debt_pcent = 100*a_debt_amt/(a_debt_amt+b_debt_amt); if 90 < a_debt_pcent < 100; run; WANT: a_debt_amt b_debt_amt a_debt_pcent 240 20 92.307692308 95 5 95 What I am getting (I don't want that, it is wrong): a_debt_amt b_debt_amt a_debt_pcent 10701.54 0 100 84256.96 0 100 240 20 92.307692308 23876.83 0 100 95 5 95

newboy1218 · ‎04-25-2022

Sorry I don't think this work.. It gives me '50' in all output now =/

newboy1218 · ‎04-25-2022

Hi, I am trying to calculate the % of A debt amount within the sum of A + B debt amount, and then I want to filter out % of A debt amount is between 90 - 100% (excluding 90% and 100%). However, in my result, it return answer where a_debt_pcent = 100.. Do you know why even though I excluded 100%? Thanks. data aa; input a_debt_amt b_debt_amt; datalines; 10701.54 0 84256.96 0 21462.54 0 240 20 23876.83 0 95 5 396 184 982 3874 0 9128 ; run; data bb; set aa; a_debt_pcent = 100*a_debt_amt/(a_debt_amt+b_debt_amt); if 90 < a_debt_pcent < 100; run; WANT: a_debt_amt b_debt_amt a_debt_pcent 240 20 92.307692308 95 5 95 What I am getting (I don't want that, it is wrong): a_debt_amt b_debt_amt a_debt_pcent 10701.54 0 100 84256.96 0 100 240 20 92.307692308 23876.83 0 100 95 5 95

newboy1218 · ‎04-22-2022

Hi, I am trying to calculate the present value by using the FINANCE function in SAS. However, I found that when my rows having interest as 0%, the log threw me error for example saying: NOTE: Invalid argument to function FINANCE('pv',0,24,-100) at line 28 column 7. What should I do to solve it? Here is the code I used: data aa; input int_rate annual_payment; datalines; 0 1200 0 4000 0.008325 1000 0.008325 500 ; run; data bb; set aa; pv = finance('pv', int_rate, 24, - annual_payment / 12); run; Sample data - HAVE: # int_rate annual_payment 1 0 1200 2 0 4000 3 0.008325 1000 4 0.008325 500 WANT: # int_rate annual_payment pv 1 0 1200 2400 2 0 4000 8000 3 0.008325 1000 1806.0852117 4 0.008325 500 903.04260585

newboy1218 · ‎02-22-2022

Hi, I have 2 datasets where dataset A contains the column headers and dataset B contains the raw data. Is there an efficient way to change the current (old) column header in dataset B according to the value in dataset A? HAVE-Dataset A: F1 F2 F3 ... F1000 account_id name age ... zip_code HAVE-Dataset B: F1 F2 F3 ... F1000 10000 John White 34 ... M8DO0U 10001 Amy Power 12 ... A7DG2E WANT-Dataset B: account_id name age ... zip_code 10000 John White 34 ... M8DO0U 10001 Amy Power 12 ... A7DG2E

newboy1218 · ‎12-05-2021

Hi All, I need to work with a JSON text string column from a SAS dataset. Below is sample records of this column. ID Data 1 {"name":"John Peter","email":"john.peter@outlook.com", "attachments":[]} 2 {"name":"Mary White","email":"mary1111@outlook.com", "attachments":[{"documentId":"a284h162","name":"AlainDLback.jpg","result":{"status":"approve","time":"2021-03-05"}}]} Desired output is to transform the dataset to the below form. ID name email attachments_documentId attachments_name attachments_result_status attachments_result_time 1 John Peter john.peter@outlook.com 2 Mary White mary1111@outlook.com a284h162 AlainDLback.jpg approve 2021-03-05 Any pointers on the approach to follow would be very helpful. Thank you!

newboy1218 · ‎08-13-2021

Still have error: ERROR: Write access to member EMWS1.EMCODE_TRAIN.VIEW is denied. ERROR: Unable to save DATA STEP view EMWS1.EMCODE_TRAIN. How do people usually create new variable (by over-writing the dataset) in SAS EM?

newboy1218 · ‎08-13-2021

Hi, I want to create a new variable based on an original variable in my dataset by using the SAS Code node. However, when I wrote the following code into the Score Code panel, it gave me error. data &EM_EXPORT_TRAIN.; set &EM_IMPORT_DATA.; if MISSING(PYMT_RATIO) then PYMT_RATIO_grp = 1; else if NOT MISSING(PYMT_RATIO) and PYMT_RATIO < 0.29 then PYMT_RATIO_grp = 2; else if NOT MISSING(PYMT_RATIO) and 0.29 <= PYMT_RATIO AND PYMT_RATIO < 1 then PYMT_RATIO_grp = 3; else if (NOT MISSING(PYMT_RATIO) and 1 <= PYMT_RATIO AND PYMT_RATIO < 1.44) or (9999 <= PYMT_RATIO) then PYMT_RATIO_grp = 4; else if NOT MISSING(PYMT_RATIO) and 1.44 <= PYMT_RATIO AND PYMT_RATIO < 9999 then PYMT_RATIO_grp = 5; run; The error is: ERROR: Unable to create EMWS1.EMCODE_TRAIN.DATA because EMWS1.EMCODE_TRAIN.VIEW already exists. Does anyone know how I can fix it?

Online Status	Offline
Date Last Visited	‎02-13-2023 03:20 PM

Calculate previous accounts' balance

Re: SAS PMT Function vs. Excel PMT Function

SAS PMT Function vs. Excel PMT Function

Re: Payment Formula

Re: Payment Formula

Payment Formula

Count cumulative obs by category

Numeric division less than or greater than error

Re: Numeric condition error

Numeric condition error

Re: ERROR CODES AND ERROR MESSAGES

Re: Numeric division less than or greater than error

Re: Numeric division less than or greater than error

Re: Numeric division less than or greater than error

Re: Invalid argument to function FINANCE 'pv' with 0 interest rate

Re: Payment Formula

Re: SAS Macro Remote Submit Complete Instantly Without Even Running It

Calculate previous accounts' balance

Re: SAS PMT Function vs. Excel PMT Function

SAS PMT Function vs. Excel PMT Function

Re: Payment Formula

Re: Payment Formula

Payment Formula

Count cumulative obs by category

Numeric division less than or greater than error

Re: Numeric condition error

Numeric condition error

Invalid argument to function FINANCE 'pv' with 0 interest rate

Rename columns based on values from other dataset

How to parse JSON array and store values in SAS Column

Re: SAS Enterprise Miner - Create New Variable Using SAS Code Node

SAS Enterprise Miner - Create New Variable Using SAS Code Node