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

BaalaRaaji — Mon, 30 Jul 2018 00:11:33 GMT

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,

Re: how to pick different dates by branch and id numbers using first and last id ..

novinosrin — Mon, 30 Jul 2018 00:36:00 GMT

data want; set have; by id b_number date; if first.date; run; untested

Re: how to pick different dates by branch and id numbers using first and last id ..

ballardw — Mon, 30 Jul 2018 14:53:23 GMT

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.

topic Re: how to pick different dates by branch and id numbers using first and last id .. in SAS Programming

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

Re: how to pick different dates by branch and id numbers using first and last id ..

Re: how to pick different dates by branch and id numbers using first and last id ..