About SNG1

SNG1 · ‎10-22-2019

Thanks ballardw for the response - The actual output data just needs to be the Loan_ID that meets the condition - 3 Consecutive Payments where each payment is >= scheduled payment I just created the FLAG (i.e. Y or N) so that it would be easier to understand. Basically I only need to keep the Loan_ID that has 3 consecutive Y and for each of the 3 the Payment should be >= scheduled payment So my output should be only the Loan_id'S that satisfy the above and for this data set it is 4,5,8189 Hope this helps

SNG1 · ‎10-22-2019

Hi All, I have a Data Set which kind of looks like below - Loan_ID Sys_Date Payment Scheduled Payment Flag 4 8-Feb-18 $135.96 $135.96 Y 4 22-Feb-18 $135.96 $135.96 Y 4 8-Mar-18 $135.96 $135.96 Y 4 22-Mar-18 $135.96 $135.96 Y 4 5-Apr-18 $135.96 $135.96 Y 4 19-Apr-18 $135.96 $135.96 Y 4 3-May-18 $135.96 $135.96 Y 4 17-May-18 $135.96 $135.96 Y 4 31-May-18 $135.96 $135.96 Y 4 14-Jun-18 $135.96 $135.96 Y 4 28-Jun-18 $135.96 $135.96 Y 4 12-Jul-18 $135.96 $135.96 Y 4 26-Jul-18 $4,702.18 $135.96 Y 5 15-Feb-18 $0.00 $137.52 N 5 1-Mar-18 $137.52 $137.52 Y 5 15-Mar-18 $137.52 $137.52 Y 5 29-Mar-18 $137.52 $137.52 Y 5 12-Apr-18 $0.00 $137.52 N 8189 17-Aug-18 $51.44 $51.44 Y 8189 31-Aug-18 $51.44 $51.44 Y 8189 14-Sep-18 $0.00 $51.44 N 8189 28-Sep-18 $51.44 $51.44 Y 8189 12-Oct-18 $0.00 $51.44 N 8189 26-Oct-18 $0.00 $51.44 N 8189 9-Nov-18 $51.44 $51.44 Y 8189 15-Nov-18 $202.88 $51.44 Y 8189 23-Nov-18 $51.44 $51.44 Y 8189 7-Dec-18 $51.44 $51.44 N 8189 21-Dec-18 $51.44 $51.44 N 9999 17-Aug-18 $99.30 $99.30 Y 9999 31-Aug-18 $0.00 $99.30 N 9999 5-Sep-18 $99.30 $99.30 Y 9999 14-Sep-18 $99.30 $99.30 Y 9999 28-Sep-18 $0.00 $99.30 N 9999 29-Sep-18 $99.30 $99.30 Y 9999 12-Oct-18 $99.30 $99.30 Y 9999 16-Oct-18 $49.30 $99.30 Y 9999 25-Oct-18 ($149.30) $99.30 N 9999 26-Oct-18 $99.30 $99.30 Y 1111 28-Sep-18 $50.00 $50.00 Y 1111 29-Sep-18 $50.00 $50.00 Y 1111 12-Oct-18 $30.00 $50.00 N 1111 16-Oct-18 $0.00 $50.00 N 1111 25-Oct-18 $50.00 $50.00 Y 1111 26-Oct-18 $0.00 $50.00 N Loan_ID, Sys_Date, Payment (which is the Paid Amount), Scheduled Payment (which is the scheduled Payment amount for the Loan), Flag (Y = Successful Payment; N = Unsuccessful Payment). From the data set below I only need the ID's which have 3 consecutive payments (given the condition that PAYMENT >= SCHEDULED_PAYMENT) For Example from the above data I only need to keep the following ID's (Loan_id 4,5 & 8189) I need to keep loan_ID 4 as it meets 3 consecutive Payments (I.E. Each Payment is >= Scheduled Payment and as per Y in the flag field they were all Successful) I need to keep loan_ID 5 AS THIS ALSO HAS 3 CONSECUTIVE PAYMENTS (i.e. Three times FLAG = Y and payment >= scheduled payment) I need to keep 8189 this also has an instance of three consecutive payments (i.e. i.e. Three times FLAG = Y and payment >= scheduled payment) I need to EXCLUDE 9999 as this does not meet my criteria, you can see that even though there are times FLAG = Y but one of the amounts in Payments ($49.30) is < Scheduled Payment ($99.30) therefore this has to be excluded. i NEED TO EXCLUDE 1111 AS IT DOES NOT MEET THE 3 Consecutive payments criteria I have tried different variations but I am unable to get to the final result which should only Show Loan_ID 4 5 8189 Any help in solving this dilemma is much appreciated!

SNG1 · ‎08-22-2019

Hi I have a Data Set that looks like below: It has an ID and then values bimonthly from 201802 onward, you can see that if the start date is 201802 then the values start from 201802, if its 201806 then they will start from 201806 ID Start Date 201802 201804 201806 201808 201810 201812 201902 201904 201906 201908 112 201802 500 210 525 555 565 555 598 600 521 123 231 201904 500 25 365 367 201806 100 256 586 265 254 2156 145 241 856 201908 255 245 201812 258 125 582 254 153 I need the following as my output - ID Start Date Refresh1 Refresh2 Refresh3 Refresh4 Refresh5 Refresh6 Refresh7 Refresh8 Refresh9 Refresh10 112 201802 500 210 525 555 565 555 598 600 521 123 231 201904 500 25 365 367 201806 100 256 586 265 254 2156 145 241 856 201908 255 245 201812 258 125 582 254 153 So basically all the values from the first column shift to the left and start from there....i am Just lost in how to accomplish this, any help is much appreciated Thanks!

SNG1 · ‎08-22-2019

Thanks a Lot for the Replies both

SNG1 · ‎08-21-2019

Hi All, I am trying to create a SAS data set which already has two columns with various data, what I need is to create another column which repeats certain numbers (that I can specify) and these repeat a certain number of time (which I can specify). For Example Column A & B denotes random data, looking for a way to create column C Repeat 1 3 times, repeat 2 2 times, repeat 3 4 times, etc. A B C 3456 234 1 4563456 3566 1 456456 145345 1 6876 7567 2 3554 345435 2 32454 8769 3 13135 345 3 45647 45345 3 65923 345353 3 I need to be able to specify the number I see in column C (i.e. 1,2,3) as well as the number of times I want them to appear (i.e. 1 to appear 3 times, 2 to appear 2 times and 3 to appear 4 times) So essentially repeating values in a new column. Any help is much appreciated

SNG1 · ‎06-19-2019

Thanks the version Is SAS i see if SAS 9.4

SNG1 · ‎06-19-2019

Its SAS Enterprise GUIDE 7.1 and I am viewing the data in the EG Grid.

SNG1 · ‎06-19-2019

Thanks for the Quick Reply: I have the following in THE LIBRARY Statement - libname wc13b odbc dsn='wc13b' readbuff=3000 insertbuff=3000 dbcommit=3000 READ_LOCK_TYPE=NOLOCK; I have also tried it with - libname wc13b odbc dsn='wc13b' READ_LOCK_TYPE=NOLOCK; My program to the pull the data is very simple - Proc Sql; create table SASUSER.clients_B as select distinct Power_ID, input(compress(phonecell),10.) as Cell_Phone, LSTNAME, FIRSTNAME, input(HAREA, 10.) as HAREA, input(HPHONE, 10.) as HPHONE, input(catx('',HAREA, HPHONE),10.) AS Home_Phone from wc13.clients ;quit; The clients table gets locked

SNG1 · ‎06-19-2019

My Apologies, its the table that I am connecting to that gets locked.

SNG1 · ‎06-19-2019

Hi Anybody has any solution for these? Much Appreciated! Thanks

SNG1 · ‎06-17-2019

Hello All, I am using SAS EG 7.1 (32 bit), I am connecting to a SQL Server via ODBC Data Source Administrator (using the SQL Server Driver). Using the above I run into issues where when I run a Program, it locks the entire DB I am connecting to...I do use a NOLOCK option in my library statement i.e. READ_LOCK_TYPE = nolock; Even with this statement I sometimes end up locking the entire DB, is there something that I am missing? Is there a statement that I need to add in the Program itself to prevent this from happening? Thanks for all your Help

SNG1 · ‎01-14-2019

Nope it does not

SNG1 · ‎01-14-2019

Thanks...that is helpful I can transpose the dates and cash flow...but the issue I am facing is how do I write that in the npv formula in SAS: For Example: I can write - PV_at_eff_date=finance('xnpv',59.85/100,d1-dn,c1-cn) But D1 to Dn is the number of columns created for Days and c1 - cn is the number or columns created for cashflow So for example ID 29 would be D1,D2,D3,...D53 for days and C1,C2,C3,...C53 for cashflow ID 31 would be D1,D2,D3,...D22 for days and C1,C2,C3,...C22 for cashflow How do I incorporate this into the NPV formula to give me the NPV of each ID using the different column numbers for Dates and CashFlow

SNG1 · ‎01-14-2019

Hello All, I have a Data Set with about 3M rows...I am trying to create a NPV calculation using SAS: the data looks like below - The formula in excel that I am trying to emulate is =XNPV(59.85/100,$D$2:$D$53,$C$2:$C$53) the portion $D$2:$D$53,$C$2:$C$53 will be different for each ID for example the formula for ID = 29 is =XNPV(59.85/100,$D$2:$D$53,$C$2:$C$53) The formula for ID = 31 is =XNPV(59.85/100,$D$54:$D$79,$C$54:$C$79) The issue I run into is to how to let SAS know where to start and stop the calculation for each ID Hope someone can HELP me as I tried a couple of things and it seems SAS is unable to read the formula in an array and I have to transpose column C and column D to get the end result... ID Date Cash Flow PV 29 2018/02/05 3,500.00 1295.975 29 2018/02/09 (57.56) 1295.975 29 2018/02/16 (57.56) 1295.975 29 2018/02/23 (57.56) 1295.975 29 2018/03/02 (57.56) 1295.975 29 2018/03/09 (57.56) 1295.975 29 2018/03/16 (57.56) 1295.975 29 2018/03/23 (57.56) 1295.975 29 2018/03/29 (57.56) 1295.975 29 2018/04/06 (57.56) 1295.975 29 2018/04/13 (57.56) 1295.975 29 2018/04/20 (57.56) 1295.975 29 2018/04/27 (57.56) 1295.975 29 2018/05/04 (57.56) 1295.975 29 2018/05/11 (57.56) 1295.975 29 2018/05/18 (57.56) 1295.975 29 2018/05/25 (57.56) 1295.975 29 2018/06/01 (57.56) 1295.975 29 2018/06/08 (57.56) 1295.975 29 2018/06/15 (57.56) 1295.975 29 2018/06/22 (57.56) 1295.975 29 2018/06/29 (57.56) 1295.975 29 2018/07/06 (57.56) 1295.975 29 2018/07/13 (57.56) 1295.975 29 2018/07/20 (57.56) 1295.975 29 2018/07/27 (57.56) 1295.975 29 2018/08/03 (57.56) 1295.975 29 2018/08/10 (57.56) 1295.975 29 2018/08/17 (57.56) 1295.975 29 2018/08/24 (57.56) 1295.975 29 2018/08/31 (57.56) 1295.975 29 2018/09/06 57.56 1295.975 29 2018/09/06 50.00 1295.975 29 2018/09/07 (57.56) 1295.975 29 2018/09/11 (57.56) 1295.975 29 2018/09/14 (57.56) 1295.975 29 2018/09/21 (57.56) 1295.975 29 2018/09/28 (57.56) 1295.975 29 2018/10/05 (57.56) 1295.975 29 2018/10/12 (57.56) 1295.975 29 2018/10/19 (57.56) 1295.975 29 2018/10/26 (57.56) 1295.975 29 2018/11/02 (57.56) 1295.975 29 2018/11/09 (57.56) 1295.975 29 2018/11/16 (57.56) 1295.975 29 2018/11/23 (57.56) 1295.975 29 2018/11/30 (57.56) 1295.975 29 2018/12/07 (57.56) 1295.975 29 2018/12/14 (57.56) 1295.975 29 2018/12/21 (57.56) 1295.975 29 2018/12/28 (57.56) 1295.975 29 2019/01/04 (57.56) 1295.975 31 2018/09/08 5,000.00 3514.378 31 2018/09/14 (68.96) 3514.378 31 2018/09/21 (68.96) 3514.378 31 2018/09/28 (68.96) 3514.378 31 2018/10/05 (68.96) 3514.378 31 2018/10/12 (68.96) 3514.378 31 2018/10/19 (68.96) 3514.378 31 2018/10/26 (68.96) 3514.378 31 2018/11/02 (68.96) 3514.378 31 2018/11/09 (68.96) 3514.378 31 2018/11/15 68.96 3514.378 31 2018/11/15 50.00 3514.378 31 2018/11/16 (68.96) 3514.378 31 2018/11/16 (118.96) 3514.378 31 2018/11/23 (68.96) 3514.378 31 2018/11/30 (68.96) 3514.378 31 2018/12/07 (68.96) 3514.378 31 2018/12/14 (68.96) 3514.378 31 2018/12/19 50.00 3514.378 31 2018/12/19 68.96 3514.378 31 2018/12/20 (68.96) 3514.378 31 2018/12/21 (68.96) 3514.378 31 2018/12/27 (18.96) 3514.378 31 2018/12/28 (68.96) 3514.378 31 2019/01/04 (68.96) 3514.378 31 2019/01/04 (500.00) 3514.378

SNG1 · ‎10-08-2018

Hi All, I have a data set which has addresses that are all messy, it looks line for example - 2303 215 Queen St E 123 21 Apple Rd 02 1 Yonge St E 64 Ardwick Blvd 16 Kelman Crt I want the result as follows - I want to exclude any observation that has multiple spaces inform of an alphabet, that is I only want 64 Ardwick Blvd and 16 Kelman Crt in my dataset and exclude everything else. Also id there are two spaces in front of the alphabet then I need to keep this just in case if there is any observation like - 64 Ardwick Blvd (i.e. 2 spaces after 64) or 64 Ardwick Blvd (i.e. 3 spaces after 64) then I need to include these as 64 Ardwick Blvd. Any help is much appreciated... Thanks!

Online Status	Offline
Date Last Visited	‎01-08-2020 04:05 PM

Re: Selecting ID's with 3 consecutive Observations

Selecting ID's with 3 consecutive Observations

Moving Columns to the left for every Row

Re: Repeating Values in New Column

Repeating Values in New Column

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: Selecting ID's with 3 consecutive Observations

Selecting ID's with 3 consecutive Observations

Moving Columns to the left for every Row

Re: Repeating Values in New Column

Repeating Values in New Column

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

Re: SAS Connection to the ODBC Server - Locks the Database

SAS Connection to the ODBC Server - Locks the Database

Re: Calculating NPV in SAS with dates and amount

Re: Calculating NPV in SAS with dates and amount

Calculating NPV in SAS with dates and amount

Cleanup address data set