Hi ,
I have dataset as below:
data have;
attrib to_dt format=date9. ;
attrib from_dt format=date9. ;
input IDl ID2 ID3 ID4 ID5 FROM_DT: date9. TO_DT:date9. Amount;
datalines;
10 12 100 1 4 31DEC2019 31DEC2021 100.00
10 12 100 1 4 31DEC2012 31DEC2019 100.00
10 12 101 4 4 31DEC2019 31DEC2021 200.00
10 12 101 4 4 31DEC2012 31DEC2019 200.00
10 12 103 6 4 31DEC2012 31DEC2019 300.00
10 12 103 6 4 31DEC2019 31DEC2021 300.00
10 12 104 7 4 31DEC2012 31DEC2019 400.00
10 12 104 7 4 31DEC2019 31DEC2021 400.00
11 13 105 3 6 01JUL2019 31DEC4747 500.00
11 13 105 3 6 01OCT2019 31DEC4747 500.00
11 14 106 4 4 01OCT2019 31DEC4747 600.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00
;
I want as below:
Dataset 1:
10 12 100 1 4 31DEC2012 31DEC2021 100.00
10 12 101 4 4 31DEC2012 31DEC2021 200.00
10 12 103 6 4 31DEC2012 31DEC2021 300.00
10 12 104 7 4 31DEC2012 31DEC2021 400.00
11 13 105 3 6 01OCT2019 31DEC4747 500.00
11 14 106 4 4 01OCT2019 31DEC4747 600.00
Dataset 2:
11 13 105 3 6 01JUL2019 31DEC4747 500.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00
1. if Amt were the same and no overlapping dates i.e. if I take the first two rows then take min(to_date) and max(from_date)
10 12 100 1 4 31DEC2019 31DEC2021 100.00
10 12 100 1 4 31DEC2012 31DEC2019 100.00
should become
10 12 100 1 4 31DEC2012 31DEC2021 100.00
2. if amt are same or different and if the dates are overlapping for ex: below rows
11 14 106 4 4 010CT2019 31DEC4747 600.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00
Then select the first row which has latest date and write the second record to new dataset.
I tried all means but could not successed so please help in this,
Thanks,
Jayaditya
@Jayaditya wrote:
Hi ,
I have dataset as below:
data have;
attrib to_dt format=date9. ;
attrib from_dt format=date9. ;
input IDl ID2 ID3 ID4 ID5 FROM_DT: date9. TO_DT:date9. Amount; T1 t2
datalines;
10 12 100 1 4 31DEC2019 31DEC2021 100.00
10 12 100 1 4 31DEC2012 31DEC2019 100.00
10 12 101 4 4 31DEC2019 31DEC2021 200.00
10 12 101 4 4 31DEC2012 31DEC2019 200.00
10 12 103 6 4 31DEC2012 31DEC2019 300.00
10 12 103 6 4 31DEC2019 31DEC2021 300.00
10 12 104 7 4 31DEC2012 31DEC2019 400.00
10 12 104 7 4 31DEC2019 31DEC2021 400.00
11 13 105 3 6 01JUL2019 31DEC4747 500.00
11 13 105 3 6 01OCT2019 31DEC4747 500.00
11 14 106 4 4 01OCT2019 31DEC4747 600.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00;
I want as below:
Dataset 1:
10 12 100 1 4 31DEC2012 31DEC2021 100.00
10 12 101 4 4 31DEC2012 31DEC2021 200.00
10 12 103 6 4 31DEC2012 31DEC2021 300.00
10 12 104 7 4 31DEC2012 31DEC2021 400.00
11 13 105 3 6 01OCT2019 31DEC4747 500.00
11 14 106 4 4 01OCT2019 31DEC4747 600.00Dataset 2:
11 13 105 3 6 01JUL2019 31DEC4747 500.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00
1. if Amt were the same and no overlapping dates i.e. if I take the first two rows then take min(to_date) and max(from_date)
10 12 100 1 4 31DEC2019 31DEC2021 100.00
10 12 100 1 4 31DEC2012 31DEC2019 100.00should become
10 12 100 1 4 31DEC2012 31DEC2021 100.00
2. if amt are same or different and if the dates are overlapping for ex: below rows
11 14 106 4 4 010CT2019 31DEC4747 600.00
11 14 106 4 4 01JUL2019 31DEC4747 700.00Then select the first row which has latest date and write the second record to new dataset.
I tried all means but could not successed so please help in this,
Thanks,
Jayaditya
I colour coded the data to distribute. Is that correct?
I suppose the identiifier is ID1 to ID5?
Do you only have 2 lines per identifier?
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
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.
Ready to level-up your skills? Choose your own adventure.