Date Manipulation

[newsas007]

Hi All,

Here is my dataset. The rule is as follows:

Please see the data 'Want'. Admin1 or the first administration always has to occur between 12/15/2019 – 3/15/2020. Therefore the admin1 date is missing for ID#1. Same scenario with ID#5 where both admin dates occur after 3/15/2020. I also want to combine the characters in the Drug and the RR variables.

Data: Have

ID	Drug	Admin	RR
1	a	7/27/2020	IVES
2	b	2/15/2020	SubCu
3	a	3/22/2020	SubCu
4	a	1/13/2020	SubCu
4	b	4/20/2020	SubCu
4	c	7/20/2020	SubCu
5	a	4/19/2020	SubCu
5	b	7/19/2020	SubCu
6	a	1/19/2020	SubCu
6	b	7/17/2020	SubCu
7	c	12/27/2019	SubCu
7	d	6/26/2020	SubCu
8	a	3/12/2020	SubCu
8	b	9/12/2020	SubCu
8	c	9/21/2020	IV
8	c	10/12/2020	IV

Data: Want

ID	Drug	Admin1	RR	Admin2	Admin3	Admin4
1
2	b	2/15/2020	SubCu
3	a	3/22/2020	SubCu
4	a,b,c	1/13/2020	SubCu	4/20/2020	7/20/2020
5
6	a,b	1/19/2020	SubCu	7/17/2020
7	c,d	12/27/2019	SubCu	6/26/2020
8	a,b,c	3/12/2020	SubCu, IV	9/12/2020	9/21/2020	10/12/2020

Thank you! 

[Ksharp · Accepted solution]

data have;
infile cards expandtabs truncover;
input ID	Drug $	Admin : mmddyy10.	RR $;
format Admin  mmddyy10.;
cards;
1	a	7/27/2020	IVES
2	b	2/15/2020	SubCu
3	a	3/22/2020	SubCu
4	a	1/13/2020	SubCu
4	b	4/20/2020	SubCu
4	c	7/20/2020	SubCu
5	a	4/19/2020	SubCu
5	b	7/19/2020	SubCu
6	a	1/19/2020	SubCu
6	b	7/17/2020	SubCu
7	c	12/27/2019	SubCu
7	d	6/26/2020	SubCu
8	a	3/12/2020	SubCu
8	b	9/12/2020	SubCu
8	c	9/21/2020	IV
8	c	10/12/2020	IV
;
data temp;
 array d{99} $ 40 _temporary_;
 array r{99} $ 40 _temporary_;
 call missing(of d{*} r{*});
 i=0;j=0;
 do until(last.id);
   set have;
   by id;
   if drug not in d then do;i+1;d{i}=drug;end;
   if rr not in r then do;j+1;r{j}=rr;end;
 end;
 _drug=catx(',',of d{*});
 _rr=catx(',',of r{*});
 do until(last.id);
   set have;
   by id;
   output;
 end;
 keep id _drug _rr admin;
run;
proc transpose data=temp out=temp1(drop=_name_) prefix=admin_;
by id _drug _rr;
var admin;
run;
data want;
 set temp1;
 if admin_1<'15dec2019'd or admin_1>'15mar2020'd then call missing(_drug,_rr,of admin_:);
run;