topic Re: Arranging data by times in New SAS User

Arranging data by times

TOTI — Sun, 21 Feb 2021 13:27:40 GMT

Hii

thanks

Re: Arranging data by times

PaigeMiller — Sun, 21 Feb 2021 13:34:36 GMT

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/

Re: Arranging data by times

PGStats — Sun, 21 Feb 2021 18:56:27 GMT

Re: Arranging data by times

TOTI — Mon, 22 Feb 2021 11:23:23 GMT

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

Re: Arranging data by times

PGStats — Mon, 22 Feb 2021 17:02:35 GMT

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;

Re: Arranging data by times

TOTI — Sun, 28 Feb 2021 08:00:29 GMT

Wow, thank you very much!