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: SAS Tricky Query

by Quartz | Level 8 in SAS Programming
‎04-28-2019 09:55 PM
‎04-28-2019 09:55 PM
The code is working properly but when I have added new variable(act_alloc_date)..its not working..? I have added this feild because in a month I have 4 different act_code and need the last one to order.. would you be able to advise? appreciate your help.thanks ... View more

Re: SAS Tricky Query

by Quartz | Level 8 in SAS Programming
‎04-22-2019 09:28 PM
‎04-22-2019 09:28 PM
Thanks Shmuel..Much Appreciated...     ... View more

Re: SAS Tricky Query

by Quartz | Level 8 in SAS Programming
‎04-22-2019 09:26 PM
‎04-22-2019 09:26 PM
Hi novinosrin, Super User...Thank you so much for the code. Much Appreciated.. Brilliant thinking in replicated the code..Awesome.   You are a SAS Expert.thanks again   Cheers! ... View more

SAS Tricky Query

by Quartz | Level 8 in SAS Programming
‎04-18-2019 01:53 AM
‎04-18-2019 01:53 AM
Hi Team,   I am stuck in the SAS QUery to filter the data. The below example is for one account but my orginal data got 1million accounts. Base data is the account level from 31jan2010 to 30APR2019 Sample data for one account. COLL_IDNN_HK MAT_CLREF END_OF_MONTH ACT_CODE 2CD3B 101 31Jan2010   2CD3B 101 28Feb2010   2CD3B 101 31Mar2010   2CD3B 101 30Apr2010   2CD3B 101 31May2010   2CD3B 101 30Jun2010   2CD3B 101 31Jul2010   2CD3B 101 31Aug2010   2CD3B 101 30Sep2010   2CD3B 101 31Oct2010   2CD3B 101 30Nov2010   2CD3B 101 31Dec2010   2CD3B 101 31Jan2011   2CD3B 101 28Feb2011   2CD3B 101 31Mar2011   2CD3B 101 30Apr2011   2CD3B 101 31May2011   2CD3B 101 30Jun2011   2CD3B 101 31Jul2011   2CD3B 101 31Aug2011   2CD3B 101 30Sep2011   2CD3B 101 31Oct2011   2CD3B 101 30Nov2011   2CD3B 101 31Dec2011   2CD3B 101 31Jan2012   2CD3B 101 29Feb2012   2CD3B 101 31Mar2012   2CD3B 101 30Apr2012   2CD3B 101 31May2012   2CD3B 101 30Jun2012   2CD3B 101 31Jul2012   2CD3B 101 31Aug2012   2CD3B 101 30Sep2012   2CD3B 101 31Oct2012   2CD3B 101 30Nov2012   2CD3B 101 31Dec2012   2CD3B 101 31Jan2013   2CD3B 101 28Feb2013   2CD3B 101 31Mar2013   2CD3B 101 30Apr2013   2CD3B 101 31May2013   2CD3B 101 30Jun2013   2CD3B 101 31Jul2013   2CD3B 101 31Aug2013   2CD3B 101 30Sep2013   2CD3B 101 31Oct2013   2CD3B 101 30Nov2013   2CD3B 101 31Dec2013   2CD3B 101 31Jan2014   2CD3B 101 28Feb2014   2CD3B 101 31Mar2014   2CD3B 101 30Apr2014   2CD3B 101 31May2014   2CD3B 101 30Jun2014   2CD3B 101 31Jul2014   2CD3B 101 31Aug2014   2CD3B 101 30Sep2014   2CD3B 101 31Oct2014   2CD3B 101 30Nov2014   2CD3B 101 31Dec2014   2CD3B 101 31Jan2015   2CD3B 101 28Feb2015   2CD3B 101 31Mar2015   2CD3B 101 30Apr2015   2CD3B 101 31May2015   2CD3B 101 30Jun2015   2CD3B 101 31Jul2015   2CD3B 101 31Aug2015   2CD3B 101 30Sep2015   2CD3B 101 31Oct2015   2CD3B 101 30Nov2015   2CD3B 101 31Dec2015   2CD3B 101 31Jan2016   2CD3B 101 29Feb2016   2CD3B 101 31Mar2016   2CD3B 101 30Apr2016   2CD3B 101 31May2016   2CD3B 101 30Jun2016   2CD3B 101 31Jul2016   2CD3B 101 31Aug2016   2CD3B 101 30Sep2016   2CD3B 101 31Oct2016   2CD3B 101 30Nov2016   2CD3B 101 31Dec2016   2CD3B 101 31Jan2017   2CD3B 101 28Feb2017   2CD3B 101 31Mar2017   2CD3B 101 31Mar2017 psale 2CD3B 101 30Apr2017   2CD3B 101 31May2017   2CD3B 101 30Jun2017   2CD3B 101 31Jul2017   2CD3B 101 31Aug2017   2CD3B 101 31Aug2017 sumap 2CD3B 101 30Sep2017   2CD3B 101 31Oct2017   2CD3B 101 31Oct2017 sumu 2CD3B 101 30Nov2017 sumsv 2CD3B 101 30Nov2017   2CD3B 101 31Dec2017   2CD3B 101 31Jan2018   2CD3B 101 28Feb2018   2CD3B 101 28Feb2018 judct 2CD3B 101 31Mar2018 judo 2CD3B 101 31Mar2018   2CD3B 101 30Apr2018   2CD3B 101 31May2018   2CD3B 101 31May2018 ntva 2CD3B 101 30Jun2018 vlreq 2CD3B 101 30Jun2018 poss 2CD3B 101 30Jun2018 biarr 2CD3B 101 30Jun2018   2CD3B 101 31Jul2018   2CD3B 101 31Jul2018 vrec 2CD3B 101 31Aug2018 propr 2CD3B 101 31Aug2018 nsold 2CD3B 101 31Aug2018   2CD3B 101 30Sep2018   2CD3B 101 30Sep2018 soldc 2CD3B 101 30Sep2018 soldp 2CD3B 101 31Oct2018   2CD3B 101 30Nov2018   2CD3B 101 30Nov2018 post 2CD3B 101 30Nov2018 msset 2CD3B 101 30Nov2018 woffa 2CD3B 101 30Nov2018 fin 2CD3B 101 31Dec2018   2CD3B 101 31Jan2019   2CD3B 101 28Feb2019   2CD3B 101 31Mar2019   2CD3B 101 30Apr2019         The logic I wanted to write is   First Step:   Delete the rows below oldest act_code and Delete the rows above newest act_code   Need to see like below data...   COLL_IDNN_HK MAT_CLREF END_OF_MONTH ACT_CODE 2CD3B 101 31Mar2017 psale 2CD3B 101 30Apr2017   2CD3B 101 31May2017   2CD3B 101 30Jun2017   2CD3B 101 31Jul2017   2CD3B 101 31Aug2017   2CD3B 101 31Aug2017 sumap 2CD3B 101 30Sep2017   2CD3B 101 31Oct2017   2CD3B 101 31Oct2017 sumu 2CD3B 101 30Nov2017 sumsv 2CD3B 101 30Nov2017   2CD3B 101 31Dec2017   2CD3B 101 31Jan2018   2CD3B 101 28Feb2018   2CD3B 101 28Feb2018 judct 2CD3B 101 31Mar2018 judo 2CD3B 101 31Mar2018   2CD3B 101 30Apr2018   2CD3B 101 31May2018   2CD3B 101 31May2018 ntva 2CD3B 101 30Jun2018 vlreq 2CD3B 101 30Jun2018 poss 2CD3B 101 30Jun2018 biarr 2CD3B 101 30Jun2018   2CD3B 101 31Jul2018   2CD3B 101 31Jul2018 vrec 2CD3B 101 31Aug2018 propr 2CD3B 101 31Aug2018 nsold 2CD3B 101 31Aug2018   2CD3B 101 30Sep2018   2CD3B 101 30Sep2018 soldc 2CD3B 101 30Sep2018 soldp 2CD3B 101 31Oct2018   2CD3B 101 30Nov2018   2CD3B 101 30Nov2018 post 2CD3B 101 30Nov2018 msset 2CD3B 101 30Nov2018 woffa 2CD3B 101 30Nov2018 fin   Secong Step:   Populate the missing within the value from previous month for the action code. Final dataset should be like this...   COLL_IDNN_HK MAT_CLREF END_OF_MONTH ACT_CODE 2CD3B 101 31Mar2017 psale 2CD3B 101 30Apr2017 psale 2CD3B 101 31May2017 psale 2CD3B 101 30Jun2017 psale 2CD3B 101 31Jul2017 psale 2CD3B 101 31Aug2017 psale 2CD3B 101 31Aug2017 sumap 2CD3B 101 30Sep2017 sumap 2CD3B 101 31Oct2017 sumap 2CD3B 101 31Oct2017 sumu 2CD3B 101 30Nov2017 sumsv 2CD3B 101 30Nov2017 sumsv 2CD3B 101 31Dec2017 sumsv 2CD3B 101 31Jan2018 sumsv 2CD3B 101 28Feb2018 sumsv 2CD3B 101 28Feb2018 judct 2CD3B 101 31Mar2018 judo 2CD3B 101 31Mar2018 judo 2CD3B 101 30Apr2018 judo 2CD3B 101 31May2018 judo 2CD3B 101 31May2018 ntva 2CD3B 101 30Jun2018 vlreq 2CD3B 101 30Jun2018 poss 2CD3B 101 30Jun2018 biarr 2CD3B 101 30Jun2018 biarr 2CD3B 101 31Jul2018 biarr 2CD3B 101 31Jul2018 vrec 2CD3B 101 31Aug2018 propr 2CD3B 101 31Aug2018 nsold 2CD3B 101 31Aug2018 nsold 2CD3B 101 30Sep2018 nsold 2CD3B 101 30Sep2018 soldc 2CD3B 101 30Sep2018 soldp 2CD3B 101 31Oct2018 soldp 2CD3B 101 30Nov2018 soldp 2CD3B 101 30Nov2018 post 2CD3B 101 30Nov2018 msset 2CD3B 101 30Nov2018 woffa 2CD3B 101 30Nov2018 fin   Please let me know if its not clear...thank you for helping out   Regards, RS ... View more

Every friday data to compare

by Quartz | Level 8 in SAS Programming
‎01-17-2019 07:06 PM
‎01-17-2019 07:06 PM
Hi Guys,   Can you please help me to  write a code for weekly report using weekday function and to automatically run on every monday morning.   I got daily data like below for 6 months..   calendar_date _FREQ_ Balance 10Jan2019 5180 1480096401.80 11Jan2019 5185 1479431521.37 12Jan2019 5185 1479431521.37 13Jan2019 5185 1479431521.37 14Jan2019 5183 1479938213.77 15Jan2019 5192 1481255273.06 16Jan2019 5198 1486672020.87 17Jan2019 5199 1481255277.06 18Jan2019 6000 1486672080.87   Logic for:  - - the report should run every monday to pick friday data only (like 18th jan 2019).. - the report should compare last two fridays numbers (like 11th jan 2019 and 18th jan 2019)   appreciate if you can send me the sample codes using weekdays for weekly report using daily data tables..   thanks you ... View more

Re: how to get the vintage numbers for buckets.

by Quartz | Level 8 in New SAS User
‎12-05-2018 05:39 AM
‎12-05-2018 05:39 AM
No kurtBremser .. it should be 1 as am counting from last date (monthly snap shot for 31Oct2018)...77027 series...for 101.. counting only if it's 7 and has same 7 in previous month .. ... View more

how to get the vintage numbers for buckets.

by Quartz | Level 8 in New SAS User
‎12-05-2018 03:36 AM
‎12-05-2018 03:36 AM
Hi Team,   I am working on a adhoc which needs to get the count of the months that they have been in arreas for 7(Bucket 7). Below is the sample data and the output i need.   appreciate the help and thoughts as usual.   accno month_end_dt arreas 101 31-Aug-15 7 101 30-Jul-16 7 101 31-Dec-17 0 101 30-Mar-18 2 101 31-Oct-18 7 201 31-Aug-15 7 201 30-Jul-16 7 201 31-Dec-17 7 201 30-Mar-18 7 201 31-Oct-18 7 301 31-Dec-15 7 301 31-Jan-16 7 301 28-Feb-16 0 301 31-Mar-16 1 301 31-Dec-16 3 301 31-Jan-17 5 301 28-Feb-17 6 301 31-Mar-17 0 301 31-Jan-18 3 301 31-Mar-18 3 301 30-Apr-18 7 301 30-Jun-18 7 301 31-Aug-18 7 301 31-Oct-18 7     output i need the count as below..   accno month_end_dt arreas count 101 31-Oct-18 7 1 201 31-Oct-18 7 5 301 31-Oct-18 7 4   it shouls count only if it has repeated 7 contiously for previous months may be from 2015 data.   example if it has 777701234567777...then should count the number where it starts from 7...it should be 4 (not 8).   Appreciate the logic.let me know if its not clear.thanks in advance.   Regards, RS ... View more

Re: sum function

by Quartz | Level 8 in SAS Programming
‎10-24-2018 09:53 PM
‎10-24-2018 09:53 PM
Please ignore the query.I have figure out the solution in proc sql .thanks appreciate everyone for quick replies.thank you   cheers! ... View more

sum function

by Quartz | Level 8 in SAS Programming
‎10-24-2018 08:37 PM
‎10-24-2018 08:37 PM
Hi , below is the sample dataset. date bucket bal 30-Sep-18 0                                10 30-Sep-18 1                                20 30-Sep-18 2                                30 30-Sep-18 3                                40 30-Sep-18 4                                50 30-Sep-18 5                                60 30-Sep-18 6                                70 30-Sep-18 7                                80 30-Sep-18 9                                90   I wanted to sum the values using SUM function.. if bucket in '(1,9) then balance else display the balance. like below   below table   30-Sep-18 0                                10     30-Sep-18 1                                110 20+90 bucket 1& 9) 30-Sep-18 2                                  30     30-Sep-18 3                                  40     30-Sep-18 4                                  50     30-Sep-18 5                                  60     30-Sep-18 6                                  70     30-Sep-18 7                                  80               30+ sum(of bucket >2)     90+ sum(of bucket >4)       appreciate your help.thanks RS ... View more

Re: how to read the variable that has 3 codesor more..

by Quartz | Level 8 in SAS Programming
‎08-13-2018 12:08 AM
‎08-13-2018 12:08 AM
Hi Novinosrin..   it works but for few scenario...like 'and'  spaces and comma without space is not working correctly with your code. @Patrick code works exactly I wanted...later I can remove the numbers , and  symbols. Just cleaning the data...   Thank you Novinsrin and Patrick...genies... ... View more

Re: how to read the variable that has 3 codesor more..

by Quartz | Level 8 in SAS Programming
‎08-13-2018 12:04 AM
‎08-13-2018 12:04 AM
Thanks Patrick...that works amazingly...cheers! ... View more

how to read the variable that has 3 codesor more..

by Quartz | Level 8 in SAS Programming
‎08-12-2018 10:36 PM
‎08-12-2018 10:36 PM
Hi Guys,   I have a below sample data in excel that I import into sas..   Code Code1 0122 WESXQI3C, BOUX17Q9, WESXYBWF 4512 DENX9UPH; BLUX8Z3F; DENXDQQ3 2183 MOUNTADA,   BROWND01, BROWND03 0122 CLARD 0130 BAXXB3OQ, BAXXKEQD BAXXEIAG 0130 HUNTPA1 5221 MERXKIEI, MERXDW49, 2183 LANX8M83, PROXEAKO, PROXEXMK, AABXJ2G7 4611 WEBXA56H, GERXDQN9 AEWEB GERXERSG JOYWEB 5251 DAWXR5D1   I have a master dataset to join on the code1 that has only one code indivually...so is there any function or sas code that I can run and create another dataset that has only one code1 list...i.e., to break them..   I can use SCAN but the delimiters are , and ; and & and space...all different as they entered manually in the excel data...   appreciate your advice and thoughts...   simple to get dataset like below...   code1 WESXQI3C BOUX17Q9 WESXYBWF DENX9UPH BLUX8Z3F DENXDQQ3 MOUNTADA BROWND01 BROWND03 CLARD BAXXB3OQ BAXXKEQD BAXXEIAG HUNTPA1 MERXKIEI MERXDW49 LANX8M83 PROXEAKO PROXEXMK AABXJ2G7 …. ... View more

how to pick different dates by branch and id numbers using first and l...

by Quartz | Level 8 in SAS Programming
‎07-29-2018 08:11 PM
‎07-29-2018 08:11 PM
Hi there,   I am below dataset...just took one id as sample...   Date date_hist ID_NUMBER BRANCH_NUMBER TERM_END_DATE 30Jun2018 30Jun2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 31May2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 30Apr2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 31Mar2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 28Feb2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 31Jan2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 31Dec2017 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 30Nov2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 31Oct2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 30Sep2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 31Aug2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 31Jul2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 30Jun2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 31May2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 30Apr2017 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 31Mar2017 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 28Feb2017 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 31Jan2017 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 31Dec2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 30Nov2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 31Oct2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 30Sep2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 31Aug2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 31Jul2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 30Jun2016 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 30Jun2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 31May2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 30Apr2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 31Mar2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 28Feb2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 31Jan2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 31Dec2017 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 30Nov2017 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 31Oct2017 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 30Sep2017 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 31Aug2017 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 30Jun2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 31May2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 30Apr2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 31Mar2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 28Feb2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 31Jan2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 31Dec2017 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 30Nov2017 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 31Oct2017 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 30Sep2017 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000 30Jun2018 31Aug2017 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000     I have to get the output as below...picking the different dates by id and branch_number ..   Date date_hist ID_NUMBER BRANCH_NUMBER TERM_END_DATE 30Jun2018 30Jun2018 0:00:00.000 976 212710244143 20Apr2020 0:00:00.000 30Jun2018 30Nov2017 0:00:00.000 976 212710244143 17Apr2020 0:00:00.000 30Jun2018 30Apr2017 0:00:00.000 976 212710244143 18Apr2017 0:00:00.000 30Jun2018 30Jun2018 0:00:00.000 976 212710266641 27Jul2020 0:00:00.000 30Jun2018 30Jun2018 0:00:00.000 976 212710266684 27Jul2020 0:00:00.000   any advice much appreciated...thanks   Regards, RS ... View more

Re: Find the last codes

by Quartz | Level 8 in SAS Programming
‎06-07-2018 08:27 PM
‎06-07-2018 08:27 PM
Thank you @Kurt_Bremser... I learned new techniques from you...Never used in proc sort..always used as SQL into clause..   Am still working on my query as i have to use two variables in join and sort it...i hope i will get it down soon...thanks for your help.. Cheers!     ... View more

Re: Find the last codes

by Quartz | Level 8 in SAS Programming
‎06-07-2018 04:17 AM
‎06-07-2018 04:17 AM
KurtBremser you are Genius....I need more help on this query... The first part code has 1700records with codes as F,G,H...as on 31MAR2018 I have to find for these records in historic table which has data since 2011..that has codes other than F,G,H..comparing our previous file... It has some billion records...(historic one) Can you advice how to write a code..? Appreciate your help...thank you... ... 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