Collapsing rows of consecutive days while keeping last date and most common values

[twerwath]

Thank you!! This works. I ran it on the entire dataset but found it did what was intended, but for any 'type' that had consecutive days. I only needed it done for type 'a'. I may not have been clear on this - my apologies, but it is a complicated problem that is difficult to articulate. But you seem to have found an answer.

I made it work simply by dividing it in two temporary datasets, one with type 'a' observations only and one holding the rest of the observations. I ran it on the first temp dataset and simply merged them back together and it is exactly as intended.

I believe this the second time you have found a solution for me for a problem I didn't know how to fix. I owe you a beer!

[novinosrin]

"I believe this the second time you have found a solution for me for a problem I didn't know how to fix. I owe you a beer!"--- Let me route those thanks to my personal gurus Paul D @hashman  @data_null__  and the adorable @DonH  . I recommend buying the book. There are ton of examples of this kind. Funny enough I once asked Guru @data_null__ - "is there anything you do not know about?" 🙂 I feel like I am entitled and have a personal right to bother them for knowledge. 

If it wasn't for a lot of their teaching on a personal level, I wouldn't have had the expertise that I have today. Hmm, as a matter of fact I should also include and thank @ChrisHemedinger  for getting me connected with the Guru's. 

We the community(all inclusive) are glad it worked for you. We are all a SAS family after all. 🙂 Have a great weekend.

[hashman]

@novinosrin: Thank you for the kind words, Naveen. 

[Ksharp]

You want consecutive days of type A ?

data have;
input id type $ begin_date: date9. dx1 $ dx2 $;
format begin_date date9.;
datalines;
1 A 28JAN2019 a b
1 A 29JAN2019 a b
1 A 30JAN2019 a a
1 A 31JAN2019 b b
1 B 2FEB2019  a b
2 B 29JAN2019 b c
2 A 1FEB2019  b d
2 A 2FEB2019  b d
2 A 3FEB2019  a b
2 A 4FEB2019 a d
2 A 5FEB2019 b b
;
data have;
 set have;
 by id type notsorted;
 if first.type or dif(begin_date) ne 1 then group+1;
run;



proc freq data=have(where=(type="A")) noprint;
table id*group*dx1/list out=temp1 ;
table id*group*dx2/list out=temp2 ;
run;
proc sort data=temp1;
by id group count;
run;
proc sort data=temp2;
by id group count;
run;
data mode1;
 set temp1;
 by id group;
 if last.group;
run;
data mode2;
 set temp2;
 by id group;
 if last.group;
run;

proc sql;
create table want as
 select a.*,b.dx1,c.dx2
  from (select distinct id,group,type,min(begin_date) as begin_date format=date9.,max(begin_date) as end_date format=date9.
         from have 
          where type="A"
           group by id,group) as a
   left join mode1 as b on a.id=b.id and a.group=b.group
   left join mode2 as c on a.id=c.id and a.group=c.group
union all
 select id,group,type,begin_date,., dx1, dx2
  from have 
   where type="B"
    group by id,group
order by id,type
;
quit;

[twerwath]

Hi @Ksharp 

It ran almost to the end but I received "ERROR: Sort execution failure".

[Ksharp]

OK. You could try delete "order by id,type" if you don't care order of obs .