cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BaalaRaaji
‎09-04-2024
  • 62 Posts
  • 13 Likes Given
  • 1 Solutions
  • 1 Likes Received

Re: How to pull the base data records from 12 separately monthly file

by Quartz | Level 8 in SAS Programming
‎09-04-2024 03:23 AM
‎09-04-2024 03:23 AM
Each monthly table has 5million records so need to run in loop for 12/24 months ... View more

How to pull the base data records from 12 separately monthly file

by Quartz | Level 8 in SAS Programming
‎09-04-2024 02:14 AM
‎09-04-2024 02:14 AM
Hi there,   I have a base data example (9k records for 2023 (Jan-Dec)). For these 9k records only i have to join the previous monthly datasets which are individually saved ex: acctJan22, acctFeb22,.....acctDec23 (24 months data).   Am looking to create a loop to get few columns from each datasets for last 12 months .   ex: 9k records (split by month- its has data from Jan23-Dec23)- For this dataset i have to join previous 12 months data to check their performance. like example say i have 500records for jan'23- I need to get there previous 12 month balance and buckets ..that would be from Jan22 to Jan23, For Feb'23 from base data i have to look back from Feb'22 to Feb'23...like vice versa.   i don't have sample data but hope its clear..    Could anyone share any macro loop logic. Thanks   Regards, rs   ... View more

How to find a particular number from the filed which has series of 24 ...

by Quartz | Level 8 in SAS Programming
‎05-14-2024 12:21 AM
‎05-14-2024 12:21 AM
Hi Team,   I have a variable called history which has 24 numbers in it. example (111221122121112121111121) - qualifies as Ever2 /455444443211113212211221 qualifies in Ever 4   Can you advise the logic to use to create flags like in the 'history' variable if it has numbers > 2( that comes in the complete series anytime not looking at the position)then Ever2 .     I mean in that a particular variable i have 1 2months data...if the history variable has  > 2 then Ever2=1, if it has >4 then Ever 4= 1;   thanks for the help.   examples of the records for history.   111111111111111111111111 888887654323221211111111 000000000000000000000000 000000000000000000000000 000000000088888888888888 111111111121111211111111 000000000000011111111111 000000000000000000000000 000000000000000000000000 888888888888888888888888 000000000000110111111211 000000000000000000000000 432232143221212212111111 000000000000000000000000 000000000000001111121111 000000000000000000000000 000000000000000000000000 111111211111111111112112           ... View more

Re: SAS Logic to get the list of accounts that have gap of 6 months fr...

by Quartz | Level 8 in SAS Programming
‎08-31-2021 03:49 AM
‎08-31-2021 03:49 AM
Difference - I calculated (need in logic to get these numbers) Example .. acct_nbr total_enrolled year Program Start _date End_Date Difference - I calculated (need in logic to get these numbers) xxx617 1 2019 MOR 01Feb2019 01May2019 11 01may2019-01Apr2020 xxx617 2 2020 MOR 01Apr2020 01Jun2020 11 End date of the first enrolled - start date of next enrolled - Gap months(>6 mnts) for enrolled again in the same program.. ... View more

SAS Logic to get the list of accounts that have gap of 6 months from s...

by Quartz | Level 8 in SAS Programming
‎08-31-2021 03:44 AM
‎08-31-2021 03:44 AM
Hi Team,   I need the code logic to create the base data from the existing monthly data which has all the accounts with different start and end date. For example, below is the sample data..   acct_nbr total_enrolled year Program Start _date End_Date Difference - I calculated (need in logic to get these numbers) xxx112 1 2019 MOR 01May2019 01Jul2019 1 xxx617 1 2019 MOR 01Feb2019 01May2019 11 xxx617 2 2020 MOR 01Apr2020 01Jun2020 11 xxx674 1 2019 MOR 01Mar2019 01May2019 2 xxx674 2 2019 MOR 01Jul2019 01Aug2019 2 xxx906 1 2019 MOR 01Sep2019 01Oct2019 1 xxx906 2 2019 MOR 01Nov2019 01Feb2020 1 xxx102 1 2020 MOR 01Apr2020 01Jun2020 3 xxx110 1 2020 MOR 01Apr2020 01Jul2020 4 xxx144 1 2020 MOR 01Mar2020 01May2020 2 xxx227 1 2019 MOR 01Nov2019 01Mar2020 2 xxx227 2 2020 MOR 01May2020 01Jun2020 1 xxx227 3 2020 MOR 01Jun2020 01Oct2020 1   The concept is the customer(account) has enrolled in the MOR Program many times in a year..i have to create a base data to include the accounts where the enrolled account in the MOR program(next program) has a gap of greater 6 months from start and end date from each previous enrolled times. if they have entered once then its fine to keep in records, the logic only for multiple times enrolled.   Below is the output data that needs to create. acct_nbr total_enrolled year Program Start _date End_Date xxx112 1 2019 MOR 01May2019 01Jul2019 xxx617 1 2019 MOR 01Feb2019 01May2019 xxx617 2 2020 MOR 01Apr2020 01Jun2020 xxx674 2 2019 MOR 01Mar2019 01Aug2019 xxx906 2 2019 MOR 01Sep2019 01Feb2020 xxx102 1 2020 MOR 01Apr2020 01Jun2020 xxx110 1 2020 MOR 01Apr2020 01Jul2020 xxx144 1 2020 MOR 01Mar2020 01May2020 xxx227 1 2019 MOR 01Nov2019 01Oct2020   > 6 mnts I should keep all the records even if one account has enrolled many times. < 6 months..i should consider only 1 record with first start ( first enrolled) and last end date (last enrolled) - between each enrolled is less than 6 months.   Appreciate your help and suggestion. thanks   RS   ... View more

logic to get all accounts that has balance > limit contionusly for pas...

by Quartz | Level 8 in SAS Programming
‎04-29-2020 09:52 AM
‎04-29-2020 09:52 AM
Hi Team,   Below is the sample data and i need to get all accounts that have balance > limit continuously past 3 months...     data test; input Accno$3. Month_End $10. Balance 8. Limit 8.; datalines; 402 31-Dec-19 7978.91 7500 402 31-Jan-20 8127.20 7500 402 29-Feb-20 6902.12 7500 402 31-Mar-20 8369.17 7500 405 31-Dec-19 1712.83 7500 405 31-Jan-20 1762.32 7500 405 29-Feb-20 1762.32 0 405 31-Mar-20 1762.32 0 406 31-Dec-19 7978.91 7500 406 31-Jan-20 8127.20 7500 406 29-Feb-20 6902.12 7500 406 31-Mar-20 8369.17 7500 407 31-Dec-19 8000.00 7500 407 31-Jan-20 8000.00 7500 407 29-Feb-20 8000.01 7500 407 31-Mar-20 8000.01 7500 run; ... View more

Re: Pick Monthend accounts continuously where balance greater than lim...

by Quartz | Level 8 in SAS Programming
‎04-29-2020 09:24 AM
‎04-29-2020 09:24 AM
The above logic is not working properly... just need to pull all accounts that have balance greater then Limit for the continuously for the past 3 months... ... View more

Pick Monthend accounts continuously where balance greater than limits ...

by Quartz | Level 8 in SAS Programming
‎04-29-2020 08:13 AM
‎04-29-2020 08:13 AM
Hi Guys,   I have pasted below sample data to get help on the sas logic. Question: I need to get the list of all account that  have balance greater than credit limit for continuously past 3 months(90 days). On the reporting month data(Mar20) i picked all accounts where Balance > Limit ...(around 8k acts out of 1.2million acts) I again subquery to get all the 8k accounts past 4 months data and sorted by month end .And clauluated the diff filed and flags too.   On this data (test2), I need to pull accounts comapring past 3 months (like for Mar20- comapre Dec19,Jan20,Feb200 that has contionusly negative diff i.e., balance > credit lmit.   data test; input Accno$3. Month_End $10. Balance 8. Limit 8.; datalines; 402 31-Dec-19 7978.91 7500 402 31-Jan-20 8127.20 7500 402 29-Feb-20 6902.12 7500 402 31-Mar-20 8369.17 7500 405 31-Dec-19 1712.83 7500 405 31-Jan-20 1762.32 7500 405 29-Feb-20 1762.32 0 405 31-Mar-20 1762.32 0 406 31-Dec-19 7978.91 7500 406 31-Jan-20 8127.20 7500 406 29-Feb-20 6902.12 7500 406 31-Mar-20 8369.17 7500 407 31-Dec-19 8000.00 7500 407 31-Jan-20 8000.00 7500 407 29-Feb-20 8000.01 7500 407 31-Mar-20 8000.01 7500 run; proc sort data= test; by accno /*Month_End*/; run; data test2; set test; IF Balance > Limit then Flag = 'Y' ;else Flag = 'N'; diff= Balance -Limit; run;     Output: I should get only 407 accno ...as this account has contionuly negative diff for past 90 days..   any help or advise apprecited.   thanks     ... View more

Re: New Ins and Outs- Automated(Loop)

by Quartz | Level 8 in SAS Programming
‎12-04-2019 08:41 PM
1 Like
‎12-04-2019 08:41 PM
1 Like
Thank you unison.   That worked perfectly... ... View more

Re: New Ins and Outs- Automated(Loop)

by Quartz | Level 8 in SAS Programming
‎11-20-2019 10:50 PM
‎11-20-2019 10:50 PM
This helps but not completely...I want to run the code for 6 months ...like below code but can't figure out how to get the previous month date . %macro date_loop(start,end); %let start=%sysfunc(inputn(&start,anydtdte9.)); %let end=%sysfunc(inputn(&end,anydtdte9.)); %let dif=%sysfunc(intck(month,&start,&end)); %do i=0 %to &dif; %let date=%sysfunc(intnx(month,&start,&i,E),date9.); %put &date; proc sql; create table NEW_&date. as select curr.accno,curr.balance,curr.END_OF_MONTH from Monthly_report (where=(END_OF_MONTH= "&date"d)) curr left join Monthly_report (where=(END_OF_MONTH= "&date"d)) prev /* here needs to update */ on curr.ACCNO = prev.ACCNO where prev.ACCNO is missing; quit; %end; %mend date_loop; %date_loop(31JAN2019,31OCT2019) ... View more

New Ins and Outs- Automated(Loop)

by Quartz | Level 8 in SAS Programming
‎11-20-2019 08:09 PM
‎11-20-2019 08:09 PM
Hi there,   I have got 18 months data as a monthly report. Every month i need to get the New Ins (Accounts in Current month but Not in Last month) and New Outs( Account is Last month but not in Current month)..   Below is the basic query i have written but its find of manual every month to run and save the data to the historic table . As am using this data separately in tableau to get the charts by months.   Below is my query..   /* Monthly report */ data Monthly_report; set Final_analysisSEPT19; where end_of_month >='30JUN2018'D ; run; DATA TEST; format today date9.; today=today(); call symputx ('last_month',put(intnx('month',today,-1,'e'),date9.)); /* 31OCT2019 */ call symputx ('lastp_month',put(intnx('month',today,-2,'e'),date9.)); /* 30SEP2019 */ RUN; %put &last_month. &lastp_month.; /* New ins and New Outs by each Monthly- monthly report */ data current; set Monthly_report ; where end_of_month = "&last_month."d; run; data last; set Monthly_report ; where end_of_month = "&lastp_month."d; run; proc sql; create table New_in as select * from current where accno not in (Select accno from last); quit; proc sql; create table New_out as select * from last where accno not in (Select accno from current); quit; data IMP.New_in_&last_month.; length Flag $15. Flag1 $15.; SET New_in; Flag='Monthly_Ins'; Flag1='Total_Ins'; Date="&last_month."d; format Date date9.; run; data IMP.New_in_&last_month.; length Flag $15. Flag1 $15.; SET New_out; Flag='Monthly_Outs'; Flag1='Total_Outs'; Date="&last_month."d; format Date date9.; run; DATA IMP.TOTAL_INS_OUTS; SET IMP.New_in_&last_month. IMP.New_in_&last_month.; RUN; DATA IMP.TOTAL_INS_OUTS_HIST; SET XIMP.TOTAL_INS_OUTS_HIST IMP.TOTAL_INS_OUTS; RUN;   Can we write the above query in loop or automatically runs for 6 months New Ins and Outs. Any ideas is appreciated.thanks   ... View more

Re: Searching the multiple words in a Variable

by Quartz | Level 8 in SAS Programming
‎10-02-2019 11:43 PM
‎10-02-2019 11:43 PM
Thanks Chris..   The code is working, there is no issue with quotation marks..only problem is it is not checking all the similar words that i am looking on the each groupings...below example..   if prxmatch("m/Provision Raised in Error|Provision Raised Error|LMI Claim should be lodged |Provision not required|LMI Claim should be lodged / oi",CMMT_X) > 0 then found='Provision Raised in Error'   Looks only for one word not working for multiple word search...   I did use Index but that not giving 100% result too..   So thought is any other better way to search multiple words in SAS? ... View more

Searching the multiple words in a Variable

by Quartz | Level 8 in SAS Programming
‎10-02-2019 09:41 PM
‎10-02-2019 09:41 PM
Hi there,   I have code the below logic to search the multiple words from the  comments variable(CMMT_X) but its not working properly.   DATA TEST; SET X (obs= 20); Comments= upcase (CMMT_X); X= INDEX(CMMT_X, "DECLINE IN PROPERTY"); /*x1= spedis(CMMT_X, "DECLINE IN PROPERTY");*/ got_value=SCAN(CMMT_X, "DECLINE","PROPERTY"); /**/ /* if index(CMMT_X,'DECLINE IN PROPERTY') or index(CMMT_X,'Decrease in Market Value')then RL_Group= 'Decrease to Security Value'; */ /* else if index(CMMT_X,'Customer Complaint') or index(CMMT_X,'Complaint') then RL_Group= 'Customer Complaint' ;*/ /* else if index(CMMT_X,'Customer Complaint') or index(CMMT_X,'Complaint') then RL_Group= 'Customer Complaint' ;*/ if prxmatch("m/DECLINE IN PROPERTY|Decrease in Market Value|Security Value|security property|Property damaged|Property deteriorated|Valuer Error/oi",CMMT_X) > 0 then found='Decrease to Security Value'; else if prxmatch("m/Customer Complaint|'Complaint/oi",CMMT_X) > 0 then found='Customer Complaint'; else if prxmatch("m/Fraud/oi",CMMT_X) > 0 then found='Fraud'; /* Fraud Internal / Fraud External */ else if prxmatch("m/LMI Claim due|No LMI| Delays with MIP|Non-Fair Wear and Tear / oi",CMMT_X) > 0 then found='LMI Claim Reduction'; else if prxmatch("m/Project Jefferson | Project Storm / oi",CMMT_X) > 0 then found='Project Jefferson'; else if prxmatch("m/Project Alton/ oi",CMMT_X) > 0 then found='Project Alton'; else if prxmatch("m/Process Error|Error / oi",CMMT_X) > 0 then found='Process Error'; /*|Funds credited to incorrect account|LMI maladministration|LMI Premium not paid|Release of security for nil consideration|Lender error - LVR miscalculation|Credit Officer - LVR miscalculation | Other HL funding issue | VLOC Limit not Cancelled |Lender Error - Unacceptable Security | LMI Missing documentation |Credit Officer - unacceptable security|Lender Committed | Loan not Secured at Origination |Missing security documentation |Construction Loan Progress Payments|VLOC Limit not Reduced |Lender error - missing documentation|Lender Error - Maladministration |Credit Officer - Approved Outside Policy/Poor Credit Decision | Poor Credit Maladministration |Lender Error - Maladministration |Missing security documentation | VLOC Limit not Cancelled | Other HL funding issue | Other LMI issue */ else if prxmatch("m/Provision Raised in Error|Provision Raised Error|LMI Claim should be lodged |Provision not required|LMI Claim should be lodged / oi",CMMT_X) > 0 then found='Provision Raised in Error'; else if prxmatch("m/Incomplete Building Loan|Builder/ oi",CMMT_X) > 0 then found='Incomplete Building Loan'; else if prxmatch("m/Uninsured Property|No building insurance|Fire Damage/ oi",CMMT_X) > 0 then found='Uninsured Property'; else if prxmatch("m/Valuation Procedures|Non-System Ordered Valuation|Incorrect Source Document | Incorrect Source Valuation |Incorrect Source|VAS decision not followed|Incorrect VAS input / oi",CMMT_X) > 0 then found='Valuation Procedures'; else found='Miscellaneous'; run;   Is there any other better way to write.Instead of prxmatch is index better?   Appreciate your help.thanks   Regards   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎09-04-2024 06:44 AM
Likes given to
Likes from
View All
Quick links