About vnreddy

vnreddy · ‎03-19-2020

Hi, I am expecting results something like below: Description Primary_Account_No No_apps A1_DOB A2_DOB A3_DOB A4_DOB A1_Exist_customer A2_Exist_customer A3_Exist_customer A4_Exist_customer Actual_value 12345678 3 19350119 19650420 19600507 0 Expected_value 12345678 3 19350119 19650420 19600507 0 M M M STATUS 12345678 PASSED PASSED PASSED PASSED PASSED FAILED FAILED FAILED PASSED Actual_value 12345679 3 19440223 19710705 19700720 0 Expected_value 12345679 3 19440223 19710705 19700720 0 M M M STATUS 12345679 PASSED PASSED PASSED PASSED PASSED FAILED FAILED FAILED PASSED Actual_value 12345680 2 19670201 19701019 0 0 Expected_value 12345680 2 19670201 19701019 0 0 A A STATUS 12345680 PASSED PASSED PASSED PASSED PASSED FAILED FAILED PASSED PASSED

vnreddy · ‎03-18-2020

Hi, I have two datasets with same column names I need to validate the values based on primary_account_no which column values are matching and not-matching from Old_data to New_data for e.g., below table has same values till A4_DOB in both the datasets. whereas, A1-A3 values are not matching in New_data with Old_data. 1. I need to show such difference in a new column with Old_data values. Primary_Account_No No_apps A1_DOB A2_DOB A3_DOB A4_DOB A1_Exist_customer A2_Exist_customer A3_Exist_customer 12345678 3 19350119 19650420 19600507 0 M M M 12345679 3 19440223 19710705 19700720 0 M M M could someone help me with this problem. I have more than 150 columns to compare. Thanks Vennapusa

vnreddy · ‎01-07-2020

Hi, Can someone please help me with above requirement

vnreddy · ‎01-07-2020

Hi @PaigeMiller sorry for that, actually i am looking for some thing like the attached image (previous post) output format. I have attached the sample data as well. DATA WORK.NEMO_IMPAIRMENT_TESTING_0000; 28 LENGTH 29 Stage $ 12 30 opening_stage 8 31 closing_stage 8 32 opening_balance 8 33 closing_balance 8 34 opening_period 8 35 closing_period 8 ; 36 FORMAT 37 Stage $CHAR12. 38 opening_stage BEST12. 39 closing_stage BEST12. 40 opening_balance BEST12. 41 closing_balance BEST12. 42 opening_period DATE9. 43 closing_period DATE9. ; 44 INFORMAT 45 Stage $CHAR12. 46 opening_stage BEST12. 47 closing_stage BEST12. 48 opening_balance BEST12. 49 closing_balance BEST12. 50 opening_period DATE9. 51 closing_period DATE9. ; 52 INFILE 'M:\SASWORK\_TD14660_PBS-SAS-CP01_\#LN00138' 53 LRECL=46 54 ENCODING="WLATIN1" 55 TERMSTR=CRLF 56 DLM='7F'x 57 MISSOVER 58 DSD ; 2 The SAS System 09:21 Tuesday, January 7, 2020 59 INPUT 60 Stage : $CHAR12. 61 opening_stage : BEST32. 62 closing_stage : BEST32. 63 opening_balance : BEST32. 64 closing_balance : BEST32. 65 opening_period : BEST32. 66 closing_period : BEST32. ; 67 RUN;

vnreddy · ‎01-07-2020

Stage_transfer opening_stage closing_stage opening_balance closing_balance opening_period closing_period Stage 1 to 1 1 1 37735.11 36092.9 31-Jan-19 31-Dec-19 Stage 1 to 2 1 2 55841.76 53787 31-Jan-19 31-Dec-19 Stage 1 to 3 1 3 17532.1 16256.83 31-Jan-19 31-Dec-19 Stage 2 to 1 2 1 17442.97 17115.08 31-Jan-19 31-Dec-19 Stage 2 to 2 2 2 64759.82 63959.25 31-Jan-19 31-Dec-19 Stage 2 to 3 2 3 35349.27 34116.02 31-Jan-19 31-Dec-19 Stage 3 to 1 3 1 6084.11 2227.07 31-Jan-19 31-Dec-19 Stage 3 to 2 3 2 57440.35 55322.16 31-Jan-19 31-Dec-19 Stage 3 to 3 3 3 16304.87 14736.68 31-Jan-19 31-Dec-19 Balance business rules: balance at 1 January 2019 stage 1 (in millions) sum up the opening_balance based on opening_stage 1 e.g., sum(37735.11+55841.76+17532.1) similarly stage 2 (in millions) sum up the opening_balance based on opening_stage 2 e.g., sum(17442.97+64759.82+35349.27) stage 3 (in millions) sum up the opening_balance based on opening stage 3 e.g., sum(6084.11+57440.35+16304.87) closing balance rules: balance at 31 December 2019 stage 1(in millions) sum up the closing_balance based on closing_stage 1 e.g., sum(36092.9+17115.08+2227.07) stage 2(in millions) sum up the closing_balance based on closing_stage 2 e.g., sum(53787+63959.25+55322.16) stage 3(in millions) sum up the closing_balance based on closing_stage 3 e.g., sum(16256.83+34116.02+14736.68) stage transfer business rules: transfer to stage 1: sum of closing_balance horizontal 1 to 1, 1 to 2, 1 to 3 2 to 1, 2 to 2, 2 to 3 3 to 1, 3 to 1, 3 to 3 sample example is done in screenshot Expected output:

vnreddy · ‎12-11-2019

dataset ln_mon_ref LNo 12_Month_A1 12_Month_A6 12_Month_Base 12_Month_A2 12_Month_A4 23 290.7546354 448.57426097 1880.3844491 1116.960121 406.90120716 24 0.1278847503 0.1573367909 0.5645658305 0.3878716946 0.1324158737 25 56.02135594 65.7289856 282.42013204 174.8432417 59.878547019 dataset sc_original Scenario Original 10/30/30/30 33/0/33/33/0 50/0/0/50/0 50/0/25/25/0 12_Month_A1 0.1071 0 0 0 0 12_Month_A6 0.1071 0.3 0 0 0 12_Month_Base 0.4464 0.1 0.333333333 0.5 0.5 12_Month_A2 0.2683 0.3 0.333333333 0 0.25 12_Month_A4 0.0711 0.3 0.333333333 0.5 0.25 based on LNO column 12_Month_A1 column should divide and multiply original*10/30/30/30 based on scenario column from dataset sc_original as shown below example calculation. I need four addition column sc_30_10,sc_33_33,sc_50_50,sc_50_25 each column should give different scenrio results as shown in Expected output calculation example: scenario_30_10 =290.7546354/0.1071*0+448.57426097/0.1071*0.3+1880.3844491/0.4464*0.1+1116.960121/0.2683*0.3+406.90120716/0.0711*0.3 =0.1278847503/0.1071*0+0.1573367909/0.1071*0.3+0.5645658305/0.4464*0.1+0.3878716946/0.2683*0.3+0.1324158737/0.0711*0.3 =56.02135594/0.1071*0+65.7289856/0.1071*0.3+282.42013204/0.4464*0.1+174.8432417/0.2683*0.3+59.878547019/0.0711*0.3 scenario_33_33 =290.7546354/0.1071*0+448.57426097/0.1071*0+1880.3844491/0.4464*0.333333333+1116.960121/0.2683*0.333333333+406.90120716/0.0711*0.333333333 =0.1278847503/0.1071*0+0.1573367909/0.1071*0+0.5645658305/0.4464*0.333333333+0.3878716946/0.2683*0.333333333+0.1324158737/0.0711*0.333333333 =56.02135594/0.1071*0+65.7289856/0.1071*0+282.42013204/0.4464*0.333333333+174.8432417/0.2683*0.333333333+59.878547019/0.0711*0.333333333 scenario_50_50 =290.7546354/0.1071*0+448.57426097/0.1071*0+1880.3844491/0.4464*0.5+1116.960121/0.2683*0+406.90120716/0.0711*0.5 =0.1278847503/0.1071*0+0.1573367909/0.1071*0+0.5645658305/0.4464*0.5+0.3878716946/0.2683*0+0.1324158737/0.0711*0.5 =56.02135594/0.1071*0+65.7289856/0.1071*0+282.42013204/0.4464*0.5+174.8432417/0.2683*0+59.878547019/0.0711*0.5 scenario_50_25 =290.7546354/0.1071*0+448.57426097/0.1071*0+1880.3844491/0.4464*0.5+1116.960121/0.2683*0.25+406.90120716/0.0711*0.25 =0.1278847503/0.1071*0+0.1573367909/0.1071*0+0.5645658305/0.4464*0.5+0.3878716946/0.2683*0.25+0.1324158737/0.0711*0.25 =56.02135594/0.1071*0+65.7289856/0.1071*0+282.42013204/0.4464*0.5+174.8432417/0.2683*0.25+59.878547019/0.0711*0.25 Expected output: LNo 12_Month_A1 12_Month_A6 12_Month_Base 12_Month_A2 12_Month_A4 sc_30_10 sc_33_33 sc_50_50 sc_50_25 23 290.7546354 448.574261 1880.384449 1116.960121 406.9012072 4644 4699 4968 4578 24 0.12788475 0.157336791 0.564565831 0.387871695 0.132415874 2 2 2 1 25 56.02135594 65.7289856 282.420132 174.8432417 59.87854702 696 709 737 690

vnreddy · ‎12-10-2019

Hi @Kurt_Bremser i fixed it with a small addition in code: by ID; count + 1 or 2; if not last.id then do; if month1 = . and stage = 2 then month1 = count; if month1 = . and stage = 3 then month1 = count; if month2 = . and stage = 3 then month2 = count; if month2 = . and stage = 2 then month2 = count; /* if month2 = . and stage = 2 then month2 = count;*/ if month3 = . and stage = 3 then month3 = count; revised_ = ecl; month = put(nemo_date,monname.); rc = lookup.add(); end; end; revised_ = .; month = ''; /* we now have the last observation for a given ID in the PDV */ select (stage); when (1) if month2 ne . or month3 ne . then do; count = max(month1,month2,month3);

vnreddy · ‎12-09-2019

complete error list: Business rule: when a customer make 3 months of outstanding due then he will fall back to stage 1, still we need to capture stage 2 or stage 3 month Name we missed to capture such scenarios from stage 3 to stage 1 ID December January February March April May June July August September October NovemberError_Month Correct_Month 3699400 2 2 2 3 3 3 3 3 3 2 2 1 December March 8816700 2 2 2 2 2 3 2 2 2 2 2 1 December May 1385840 2 2 2 3 1 1 1 1 1 1 1 1 December March 2473900 2 2 2 2 2 2 3 3 3 3 1 1 December June 2723940 2 3 3 3 2 2 1 1 1 1 1 1 December January 6513440 2 3 3 3 3 3 3 3 3 2 2 1 December January 6998070 1 1 1 1 1 1 2 2 3 2 2 1 June August 7778480 2 3 1 1 1 1 1 1 1 1 1 1 December January data : ID Stage ECL Nemo_date run_date 3699400 3 1862.9879699 30JUN2019 02JUL19:06:05:38 3699400 1 24.129513866 30NOV2019 03DEC19:09:04:37 3699400 3 1822.5610297 31MAR2019 02APR19:09:55:12 3699400 3 1972.1525926 31AUG2019 03SEP19:06:03:59 3699400 2 1617.4190637 31DEC2018 03JAN19:06:10:18 3699400 2 1378.1121121 31OCT2019 04NOV19:06:04:17 3699400 2 1121.5341175 30SEP2019 02OCT19:06:04:18 3699400 3 1805.0156904 31MAY2019 04JUN19:06:05:27 3699400 3 1866.5382377 30APR2019 02MAY19:06:46:31 3699400 2 1748.5576675 28FEB2019 04MAR19:10:55:37 3699400 2 1095.2350804 31JAN2019 04FEB19:06:04:44 3699400 3 1908.4267168 31JUL2019 02AUG19:16:50:19 8816700 2 1638.3432736 31OCT2019 04NOV19:06:04:17 8816700 2 1635.6142192 30SEP2019 02OCT19:06:04:18 8816700 3 1987.4549959 31MAY2019 04JUN19:06:05:27 8816700 2 1974.8782796 31AUG2019 03SEP19:06:03:59 8816700 2 1946.6432949 30APR2019 02MAY19:06:46:31 8816700 2 2188.6786306 31MAR2019 02APR19:09:55:12 8816700 2 2111.3128619 28FEB2019 04MAR19:10:55:37 8816700 2 2118.6497143 30JUN2019 02JUL19:06:05:38 8816700 1 41.213089542 30NOV2019 03DEC19:09:04:37 8816700 2 1753.7316624 31DEC2018 03JAN19:06:10:18 8816700 2 2085.2962381 31JUL2019 02AUG19:16:50:19 8816700 2 1847.9767636 31JAN2019 04FEB19:06:04:44 1372250 2 2392.9752707 30SEP2019 02OCT19:06:04:18 1372250 2 2058.4388742 31MAY2019 04JUN19:06:05:27 1372250 2 2689.4439506 30JUN2019 02JUL19:06:05:38 1372250 1 61.928508158 30NOV2019 03DEC19:09:04:37 1372250 2 2320.2470165 28FEB2019 04MAR19:10:55:37 1372250 2 2561.9891722 31JAN2019 04FEB19:06:04:44 1372250 2 2179.8143277 31OCT2019 04NOV19:06:04:17 1372250 2 2091.5381932 30APR2019 02MAY19:06:46:31 1372250 2 2233.2878468 31MAR2019 02APR19:09:55:12 1372250 2 3324.9728096 31AUG2019 03SEP19:06:03:59 1372250 2 2428.658178 31JUL2019 02AUG19:16:50:19 1372250 3 2647.8705954 31DEC2018 03JAN19:06:10:18 1385840 3 1528.3795764 31MAR2019 02APR19:09:55:12 1385840 1 33.304034973 31MAY2019 04JUN19:06:05:27 1385840 1 13.871871515 30JUN2019 02JUL19:06:05:38 1385840 2 310.29128277 31JAN2019 04FEB19:06:04:44 1385840 2 1275.3625672 28FEB2019 04MAR19:10:55:37 1385840 1 33.903650152 30APR2019 02MAY19:06:46:31 1385840 1 7.3714552937 31AUG2019 03SEP19:06:03:59 1385840 2 693.48616717 31DEC2018 03JAN19:06:10:18 1385840 1 6.9487856666 31OCT2019 04NOV19:06:04:17 1385840 1 6.8407567495 30NOV2019 03DEC19:09:04:37 1385840 1 7.5434187432 31JUL2019 02AUG19:16:50:19 1385840 1 6.7427855599 30SEP2019 02OCT19:06:04:18 2473900 2 222.76496269 31JAN2019 04FEB19:06:04:44 2473900 3 264.03817664 30JUN2019 02JUL19:06:05:38 2473900 2 210.12901395 31MAY2019 04JUN19:06:05:27 2473900 2 206.11605391 28FEB2019 04MAR19:10:55:37 2473900 1 8.2226265119 31OCT2019 04NOV19:06:04:17 2473900 3 254.37658964 30SEP2019 02OCT19:06:04:18 2473900 1 8.1958360419 30NOV2019 03DEC19:09:04:37 2473900 2 256.69015671 31DEC2018 03JAN19:06:10:18 2473900 2 242.44196173 30APR2019 02MAY19:06:46:31 2473900 3 268.45912006 31AUG2019 03SEP19:06:03:59 2473900 3 268.48143296 31JUL2019 02AUG19:16:50:19 2473900 2 233.32308168 31MAR2019 02APR19:09:55:12 2723940 3 7263.3773218 31JAN2019 04FEB19:06:04:44 2723940 1 379.83785768 30JUN2019 02JUL19:06:05:38 2723940 2 5586.2952417 31MAY2019 04JUN19:06:05:27 2723940 3 7104.6913582 28FEB2019 04MAR19:10:55:37 2723940 2 7785.6022725 31DEC2018 03JAN19:06:10:18 2723940 1 170.53867738 31OCT2019 04NOV19:06:04:17 2723940 1 220.14435729 30NOV2019 03DEC19:09:04:37 2723940 1 147.0573559 30SEP2019 02OCT19:06:04:18 2723940 2 4393.4276698 30APR2019 02MAY19:06:46:31 2723940 1 221.05083336 31AUG2019 03SEP19:06:03:59 2723940 1 217.82040237 31JUL2019 02AUG19:16:50:19 2723940 3 7815.1398112 31MAR2019 02APR19:09:55:12 6513440 2 319.90358269 31DEC2018 03JAN19:06:10:18 6513440 3 319.26679957 30JUN2019 02JUL19:06:05:38 6513440 3 328.02321885 31MAY2019 04JUN19:06:05:27 6513440 3 328.35994406 28FEB2019 04MAR19:10:55:37 6513440 2 23.823343629 31OCT2019 04NOV19:06:04:17 6513440 1 4.7580492463 30NOV2019 03DEC19:09:04:37 6513440 2 32.211037076 30SEP2019 02OCT19:06:04:18 6513440 3 331.78648164 30APR2019 02MAY19:06:46:31 6513440 3 323.09935755 31AUG2019 03SEP19:06:03:59 6513440 3 330.56146708 31JUL2019 02AUG19:16:50:19 6513440 3 330.65791555 31MAR2019 02APR19:09:55:12 6513440 3 326.15410819 31JAN2019 04FEB19:06:04:44 6998070 2 147.62658372 30JUN2019 02JUL19:06:05:38 6998070 1 5.6677217381 31MAY2019 04JUN19:06:05:27 6998070 1 11.531858765 30NOV2019 03DEC19:09:04:37 6998070 2 366.43082272 31OCT2019 04NOV19:06:04:17 6998070 1 3.7603266544 30APR2019 02MAY19:06:46:31 6998070 2 375.13558347 30SEP2019 02OCT19:06:04:18 6998070 3 905.51831349 31AUG2019 03SEP19:06:03:59 6998070 1 5.72533218 28FEB2019 04MAR19:10:55:37 6998070 1 5.7364376314 31DEC2018 03JAN19:06:10:18 6998070 1 5.7328546261 31JAN2019 04FEB19:06:04:44 6998070 1 8.6072465204 31MAR2019 02APR19:09:55:12 6998070 2 843.49509942 31JUL2019 02AUG19:16:50:19 7778480 1 15.736316063 30JUN2019 02JUL19:06:05:38 7778480 1 16.037948667 28FEB2019 04MAR19:10:55:37 7778480 1 4.8521210794 31MAY2019 04JUN19:06:05:27 7778480 1 8.2745397182 30NOV2019 03DEC19:09:04:37 7778480 1 12.664463274 31OCT2019 04NOV19:06:04:17 7778480 1 4.9715427562 30APR2019 02MAY19:06:46:31 7778480 1 5.8862327226 30SEP2019 02OCT19:06:04:18 7778480 1 15.567752577 31AUG2019 03SEP19:06:03:59 7778480 2 537.87465031 31DEC2018 03JAN19:06:10:18 7778480 3 711.39039902 31JAN2019 04FEB19:06:04:44 7778480 1 15.737619037 31MAR2019 02APR19:09:55:12 7778480 1 10.61034303 31JUL2019 02AUG19:16:50:19

vnreddy · ‎12-09-2019

hi @Kurt_Bremser sorry for bothering, i just traced out one issue from the results after using your program below id should give March Month in Month Column but we are getting December. 1584 2 2 2 3 1 1 1 1 1 1 1 1 December

vnreddy · ‎12-09-2019

hi @Kurt_Bremser in this case how can i do after your step dynamically, as you know going forward i cannot change month names manually.

vnreddy · ‎12-09-2019

Hi @Kurt_Bremser i tried something like this : 23 GOPTIONS ACCESSIBLE; 24 %let Monthn12=%sysfunc(intnx(month,%sysfunc(today()),-1),MONNAME.); 25 %put &Monthn12; November 26 27 options validvarname=any; 28 data test; 29 set nemo_rev_mth; 30 /*where ' November'n NE .;*/ 31 where &Monthn12 NE .; ERROR: Variable November is not on file WORK.NEMO_REV_MTH. 32 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 24 variables. WARNING: Data set WORK.TEST was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.01 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 627.40k OS Memory 22108.00k Timestamp 09/12/2019 12:57:18 PM working when i am using something like this where ' November'n NE .; but when i use the Macro for November month it's giving me Variable November is not available. As we know November Month is available in out dataset in numeric format

vnreddy · ‎12-06-2019

Hi @Kurt_Bremser how do i exclude the ID where last month (i.e.,November is . ) i need to delete the rows where last month has blank stage . i could do it manually by giving the November month manually NE . but, going forward i don't want to change the program every month. Thank you. vnreddy

vnreddy · ‎12-05-2019

Hi @Kurt_Bremser thank you so much, it's a data validation mistake from my end. Business rules what we have used is absolutely right. How to i remove the rows where last month is stage is missing e.g., ID 7079. Thank you so much for everything,

vnreddy · ‎12-05-2019

last month is not in stage 3, eventually business intention is to capture the stage 2 and 3 from mon1 to mon11 if you see 4745 also has 2 it captured correctly,

vnreddy · ‎12-05-2019

in some cases i come across with blank stage as well. some of the ID starts from May or June , that mean i need to overcome the blanks as well

Online Status	Offline
Date Last Visited	‎04-09-2024 11:17 PM

Re: Total and Average in Proc report

Re: Total and Average in Proc report

Re: Total and Average in Proc report

Total and Average in Proc report

Re: Excel formula in SAS

Re: Excel formula in SAS

Re: Excel formula in SAS

Excel formula in SAS

Re: Proc SQL group by is not working

Proc SQL group by is not working

Re: Total and Average in Proc report

Re: MTD %

Re: MTD %

Re: difference b/w actual and target dates

Re: Proc report text file column position

Re: clinical trail scoring

Re: clinical trail scoring

Re: How to compare value-to-value between two datasets

How to compare value-to-value between two datasets

Re: Proc report

Re: Proc report

Proc report

calculation

Re: Arrays

Re: Arrays

Re: Arrays

Re: Arrays

Re: Arrays

Re: Arrays

Re: Arrays

Re: Arrays

Re: Arrays