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!
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;
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;
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.