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
See this:
proc sort data=testl;
by policy_number from_dt;
run;
data want;
do until (last.amt);
set testl;
by policy_number amt notsorted;
if first.amt then f_dt = from_dt;
end;
from_dt = f_dt;
drop f_dt;
run;
See this:
proc sort data=testl;
by policy_number from_dt;
run;
data want;
do until (last.amt);
set testl;
by policy_number amt notsorted;
if first.amt then f_dt = from_dt;
end;
from_dt = f_dt;
drop f_dt;
run;
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.