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?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: