Sorry, my explanation was not clear. I created a new dataline . data have; 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 010CT2019 31DEC4747 600.00 11 14 106 4 4 01JUL2019 31DEC4747 700.00 ; Here Key fields are ID1, ID2, ID3,Id4,ID5. So for this combination: 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 I have to flag row 11 14 106 4 4 010CT2019 31DEC4747 600.00 as 1 and 11 14 106 4 4 01JUL2019 31DEC4747 700.00 as 0 as they are overlapping dates so I have to flag the row with latest to_date to 1. Please help me with this. Regards, Jay
... View more
Hi, data work.testl; infile datalines ; input Policy_number:$8. Test_Rk:8. Version:8. Amt:8. from_dt:date. to_dt:date.; datalines; 1 10 1 300 '01Jan2006'd '01Jan2034'd 1 10 1 700 '01Jan2007’d '01Jan2034'd I have the above example. row 1 and row 2 are overlapping i.e. from_dt of the second row (01Jan2007) is less than to_dt of the first row(01Jan2034) of the same key policy_number,test_rk, and version. In that case, I want to select the second row in one dataset and write the first row in another table. dataset 1 : 1 10 1 700 '01Jan2007’d '01Jan2034'd dataset 2: 1 10 1 300 '01Jan2006'd '01Jan2034'd Please help me with this. Thanks, Jayaditya
... View more
Hi, data work.testl; infile datalines ; input Policy_number:$8. Test_Rk:8. Version:8. Amt:8. from_dt:date. to_dt:date.; datalines; 1 10 1 300 '01Jan2006'd '01Jan2034'd 1 10 1 700 '01Jan2035’d '01Jun2035'd 1 10 1 300 '01Jan2034'd '01Jan2035'd I have the above example. In which I want the output to be the following: 1 10 1 300 '01JAN2006'd '01JUN2035'd 1 10 1 700 '01JAN2035'd '01JUN2035'd ie. if the amount is the same for the subsequent from_dt for the same Policy_number,Test_Rk, Version then the periods must be combined to one bigger period(from_dt = min(from_dt) and to_dt = Max(to_dt) - 1 10 1 300 '01JAN2006'd '01JUN2035'd if the amount is different then don't combine - 1 10 1 700 '01JAN2035'd '01JUN2035'd How can I achieve this? Many Thanks, Jay
... View more
@Kurt_Bremser: Thanks for the reply. If I also want to check on col3 as well. So can I use the condition as below: Because first, I have to check on col3 and also on col6 and do the cumulative sum. Would you please help me with this? or is it impossible, and I have to perform this action in a separate step? if first.col6 or first.col3 then amt = coalesce(amt_orig,0);
else amt = sum(amt_orig,amt);
if last.col6;
run;
... View more
Hi Kris, It does not give me the result I am looking for. I attached the input dataset as CSV. I am new to SAS. I tried as below. data new; set input (rename = (col7=AMT_ORIG)); by col1,col2,col3,col4,col5,col6; length amt 8; if(first.col4) then amt = coalesce(amt_orig,0); else amt = sum(amt_orig,amt); run; Please help me with this.
... View more
I have a dataset as below: col1 col2 col3 col4 col5 date col6 col7 10 11 1 1 2 04jul2021 11 100 10 11 2 1 2 05jul2021 12 50 10 11 2 1 2 05jul2021 13 100 11 1 1 1 1 01Jul2021 14 200 11 1 1 1 1 01jul12021 14 200 I want to sum the value in the col7 by col1, col2,col3,col4,col5,date,col6 and the resultant should be as below: col1 col2 col3 col4 col5 date col6 col7 10 11 1 1 2 04jul2021 11 100 10 11 2 1 2 05jul2021 12 50 10 11 2 1 2 05jul2021 13 100 11 1 1 1 1 01jul2021 14 400 if in above example as Col6 is different in the line 2 and 3 so col7 should not be summed(so two lines). Where as line 4 and 5 are same so it shoul be summed and in one line How can I achieve in SAS Base?
... View more