SAS Support Communities

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! ... View more

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! ... View more

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 ... View more

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 ... View more

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 ... View more

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!     ... View more