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!
"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.
@novinosrin: Thank you for the kind words, Naveen.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.