Hii
thanks
Yes, it could be a bit simpler using the fact that DO UNTIL() can refer to a variable that is not already defined:
data have;
input id1 id2 (start_date end_date) (:ddmmyy10.);
format start_date end_date yymmdd10.;
datalines;
111 1 17/01/2000 25/09/2001
111 1 17/01/2000 23/10/2001
111 1 17/01/2000 29/12/2002
123 1 27/05/1999 17/01/2000
145 1 29/12/2002 16/09/2014
;
proc sort data=have; by id1 id2; run;
data want;
do until (last.id2);
set have; by id1 id2;
sd = min(sd, start_date);
ed = max(ed, end_date);
end;
format sd ed yymmdd10.;
drop start_date end_date;
rename sd=start_date ed=end_date;
run;
proc sort data=want; by start_date id1 id2; run;
Can you explain the logic that creates the final table from the initial table?
Please post data in readable and usable form, following these instructions: https://blogs.sas.com/content/sastraining/2016/03/11/jedi-sas-tricks-data-to-data-step-macro/
Thanks for your response.
I start sas after python and r so I wanted to know how to write this program in sas and not in proc sql.
I found a solution but it is too long in my opinion.
My solution:
data have;
length start_date $10 end_date $10;
input id_1 1-3 id_2 4-5 start_date $ end_date $;
datalines;
111 1 17/01/2000 25/09/2001
111 1 17/01/2000 23/10/2001
111 1 17/01/2000 29/12/2002
123 1 27/05/1999 17/01/2000
145 1 29/12/2002 16/09/2014
;
run;
data have1;
set have;
start_date1 = input(start_date, ddmmyy10.);
end_date1 = input(end_date, ddmmyy10.);
drop start_date end_date;
rename start_date1= start_date end_date1 = end_date;
format start_date1 end_date1 ddmmyy10.;
run;
proc sort data=have1;
by id_1 id_2 start_date end_date;
run;
data want;
set have1;
by id_1 id_2 start_date end_date;
retain start_new end_new;
if first.id_2 then
do;
start_new = start_date;
end_new = end_date;
if first.id_2 and last.id_2 then
output;
end;
else if start_new=start_date then
do;
end_new=end_date;
if last.id_2 then
output;
end;
drop end_date start_date;
FORMAT start_new end_new ddmmyy10.;
run;
proc sort data=want;
by id_2 start_new end_new;
run;
Is there a way to write it in a shorter way?
Thank you very much :)
Yes, it could be a bit simpler using the fact that DO UNTIL() can refer to a variable that is not already defined:
data have;
input id1 id2 (start_date end_date) (:ddmmyy10.);
format start_date end_date yymmdd10.;
datalines;
111 1 17/01/2000 25/09/2001
111 1 17/01/2000 23/10/2001
111 1 17/01/2000 29/12/2002
123 1 27/05/1999 17/01/2000
145 1 29/12/2002 16/09/2014
;
proc sort data=have; by id1 id2; run;
data want;
do until (last.id2);
set have; by id1 id2;
sd = min(sd, start_date);
ed = max(ed, end_date);
end;
format sd ed yymmdd10.;
drop start_date end_date;
rename sd=start_date ed=end_date;
run;
proc sort data=want; by start_date id1 id2; run;
Wow, thank you very much!
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.