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
In SAS you want to be careful about the term "date". SAS will treat numeric values of the number of days since 1 Jan 1960 as dates with functions and supported formats. The values you show would be more likely datetime values as they show a time component. Datetime values are numbers of seconds.
The format you show the dates appearing as also use a MicroSlop format that SAS by default does not use for display. So I suspect that your variable is actually character which means that sort order is not going to work (30Apr will come before 30Jan).
You may wan to ensure the variable type and format used.
It is very worthwhile to have dates as SAS date values as then there are a large number of functions to manipulate the values, sort order is as expected for tables or graphs and you can create many summaries by changing formats for grouping without having to create additional variables for that purpose.
Also since NONE of the shown values have any significant time value you might be better off changing them to dates. Then the verbiage and appearance match.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.