Adding rows to long format data

[hellorc]

Closed

[Patrick]

Here you go

data have1;
  input id start stop @@;
  informat start stop mmddyy10.;
  format start stop mmddyy10.;
  datalines;
1 01/01/2019 02/01/2019 
1 02/01/2019 03/01/2019 
1 03/01/2019 04/01/2019
1 04/01/2019 05/01/2019
1 05/01/2019 06/01/2019
2 04/01/2019 05/01/2019
2 05/01/2019 06/01/2019
;
run;

data have2;
  input id surgery1 surgery2 @@;
  informat surgery1 surgery2 mmddyy10.;
  format surgery1 surgery2 mmddyy10.;
  datalines;
1 02/15/2019 04/10/2019
;
run;

/* option 1: if your source data is already sorted by id and start/surgery */
data want_1;
  set have1 have2(rename=(surgery1=start surgery2=stop));
  by id start;
run;

/* option 2: if the source data is not already sorted */
data want_2;
  set have1 have2(rename=(surgery1=start surgery2=stop));
run;
proc sort data=want_2;
  by id start;
run;

/* option 3: SQL Union */
proc sql feedback;
  create table want_3 as
    select 
      t1.id
      , t1.start
      , t1.stop
    from WORK.HAVE1 t1
    union all
    select 
      t2.id
      , t2.surgery1
      , t2.surgery2
    from WORK.HAVE2 t2
    order by id, start
  ;
quit;

[Ksharp · Accepted solution]

data have1;
  input id start stop @@;
  informat start stop mmddyy10.;
  format start stop mmddyy10.;
  datalines;
1 01/01/2019 02/01/2019 
1 02/01/2019 03/01/2019 
1 03/01/2019 04/01/2019
1 04/01/2019 05/01/2019
1 05/01/2019 06/01/2019
2 04/01/2019 05/01/2019
2 05/01/2019 06/01/2019
;
run;

data have2;
  input id surgery1 surgery2 @@;
  informat surgery1 surgery2 mmddyy10.;
  format surgery1 surgery2 mmddyy10.;
  datalines;
1 02/15/2019 04/10/2019
;
run;

data temp;
set have1;
n+1;
do date=start to stop;
 output;
end;
keep id date n;
run;
proc transpose data=have2 out=temp2;
by id;
var surgery:;
run;

proc sort data=temp;by id date;run;

data want;
 set temp temp2(keep=id col1 rename=(col1=date) in=inb );
 by id date;
 flag=inb;
 if not missing(date);
 format date mmddyy10.;
run;
data want;
 update want(obs=0) want;
 by id;
 output;
run;
data want;
 set want;
 by id n notsorted;
 if first.id then do;group=0;group2=0;end;
 if first.n then group+1;
 if flag then group2+1;
run;
proc sql;
create table final_want as
select id,group,group2,min(date) as start format=mmddyy10.,max(date) as stop format=mmddyy10.
 from want
  group by id,group,group2;
quit;

[Patrick]

---

@hellorc wrote:

Closed

---

Why did you delete your question after you received answers?

The forum Q&A is also useful to find solutions to questions that have been discussed already - but that only works if you don't delete the question.