Arranging data by times

[TOTI]

Hii

thanks

[PaigeMiller]

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/

[PGStats]

[TOTI]

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 :)

[PGStats · Accepted solution]

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;

[TOTI]

Wow, thank you very much!