Hi all,
I am trying to create a population pkpd data set with below information. For example I have TR which base dataset and EX. I am trying to merge these two. I am trying get CYCLE from EX after merging with TR but I have to end up with records in TR with the value of EX.CYCLE. this is my first time working on popPKPD. Can you please help me with this?
data tr;
input subjid $1-19 visit $20-36 adt $37-46;
datalines;
JPN-00019-81001101 SCREENING 29JAN2018
JPN-00019-81001101 END OF TREATMENT 27FEB2018
JPN-00019-81001102 SCREENING 23FEB2018
JPN-00019-81001102 END OF TREATMENT 05APR2018
JPN-00019-81001103 SCREENING 09MAY2018
JPN-00019-81001103 WEEK 6 27JUN2018
JPN-00019-81001103 END OF TREATMENT 13AUG2018
JPN-00019-81001104 SCREENING 30JUL2018
JPN-00019-81001104 WEEK 6 05SEP2018
JPN-00019-81001104 WEEK 12 18OCT2018
JPN-00019-81001104 WEEK 18 28NOV2018
JPN-00019-81001104 END OF TREATMENT 10JAN2019
JPN-00019-81001105 SCREENING 13AUG2018
JPN-00019-81001105 WEEK 6 19SEP2018
JPN-00019-81001105 WEEK 12 31OCT2018
JPN-00019-81001105 END OF TREATMENT 13DEC2018
JPN-00019-81001106 SCREENING 10SEP2018
JPN-00019-81001106 WEEK 6 24OCT2018
JPN-00019-81001106 WEEK 12 12DEC2018
JPN-00019-81001106 WEEK 18 30JAN2019
JPN-00019-81001106 WEEK 24 06MAR2019
JPN-00019-81001106 WEEK 30 17APR2019
JPN-00019-81001106 WEEK 36 29MAY2019
JPN-00019-81001106 END OF TREATMENT 07AUG2019
JPN-00019-81001107 SCREENING 19SEP2018
JPN-00019-81001107 WEEK 6 30OCT2018
JPN-00019-81001107 WEEK 12 20DEC2018
JPN-00019-81001107 END OF TREATMENT 16JAN2019
JPN-00019-81001108 SCREENING 14NOV2018
JPN-00019-81001108 END OF TREATMENT 25DEC2018
JPN-00019-81001109 SCREENING 27DEC2018
JPN-00019-81001109 WEEK 6 14FEB2019
JPN-00019-81001109 WEEK 12 04APR2019
JPN-00019-81001109 END OF TREATMENT 16MAY2019
;
run;
data ex;
input subjid $1-19 visit $20-33 cycle $34-36 exstdtc $37-50;
datalines;
JPN-00019-81001101 CYCLE 1 DAY 1 1 07FEB2018
JPN-00019-81001102 CYCLE 1 DAY 1 1 26FEB2018
JPN-00019-81001102 CYCLE 2 DAY 1 2 20MAR2018
JPN-00019-81001103 CYCLE 1 DAY 1 1 23MAY2018
JPN-00019-81001103 CYCLE 2 DAY 1 2 13JUN2018
JPN-00019-81001103 CYCLE 3 DAY 1 3 04JUL2018
JPN-00019-81001103 CYCLE 4 DAY 1 4 25JUL2018
JPN-00019-81001104 CYCLE 1 DAY 1 1 01AUG2018
JPN-00019-81001104 CYCLE 2 DAY 1 2 22AUG2018
JPN-00019-81001104 CYCLE 3 DAY 1 3 13SEP2018
JPN-00019-81001104 CYCLE 4 DAY 1 4 04OCT2018
JPN-00019-81001104 CYCLE 5 DAY 1 5 25OCT2018
JPN-00019-81001104 CYCLE 6 DAY 1 6 15NOV2018
JPN-00019-81001104 CYCLE 7 DAY 1 7 06DEC2018
JPN-00019-81001104 CYCLE 8 DAY 1 8 27DEC2018
JPN-00019-81001105 CYCLE 1 DAY 1 1 15AUG2018
JPN-00019-81001105 CYCLE 2 DAY 1 2 05SEP2018
JPN-00019-81001105 CYCLE 3 DAY 1 3 26SEP2018
JPN-00019-81001105 CYCLE 4 DAY 1 4 17OCT2018
JPN-00019-81001105 CYCLE 5 DAY 1 5 07NOV2018
JPN-00019-81001105 CYCLE 6 DAY 1 6 28NOV2018
JPN-00019-81001106 CYCLE 1 DAY 1 1 18SEP2018
JPN-00019-81001106 CYCLE 2 DAY 1 2 09OCT2018
JPN-00019-81001106 CYCLE 3 DAY 1 3 31OCT2018
JPN-00019-81001106 CYCLE 4 DAY 1 4 21NOV2018
JPN-00019-81001106 CYCLE 5 DAY 1 5 12DEC2018
JPN-00019-81001106 CYCLE 6 DAY 1 6 09JAN2019
JPN-00019-81001106 CYCLE 7 DAY 1 7 30JAN2019
JPN-00019-81001106 CYCLE 8 DAY 1 8 20FEB2019
JPN-00019-81001106 CYCLE 9 DAY 1 9 13MAR2019
JPN-00019-81001106 CYCLE 10 DAY 1 10 03APR2019
JPN-00019-81001106 CYCLE 11 DAY 1 11 24APR2019
JPN-00019-81001106 CYCLE 12 DAY 1 12 15MAY2019
JPN-00019-81001106 CYCLE 13 DAY 1 13 05JUN2019
JPN-00019-81001106 CYCLE 14 DAY 1 14 26JUN2019
JPN-00019-81001106 CYCLE 15 DAY 1 15 17JUL2019
JPN-00019-81001107 CYCLE 1 DAY 1 1 25SEP2018
JPN-00019-81001107 CYCLE 2 DAY 1 2 16OCT2018
JPN-00019-81001107 CYCLE 3 DAY 1 3 12NOV2018
JPN-00019-81001107 CYCLE 4 DAY 1 4 03DEC2018
JPN-00019-81001107 CYCLE 5 DAY 1 5 26DEC2018
JPN-00019-81001108 CYCLE 1 DAY 1 1 19NOV2018
JPN-00019-81001108 CYCLE 2 DAY 1 2 10DEC2018
JPN-00019-81001109 CYCLE 1 DAY 1 1 09JAN2019
JPN-00019-81001109 CYCLE 2 DAY 1 2 30JAN2019
JPN-00019-81001109 CYCLE 3 DAY 1 3 20FEB2019
JPN-00019-81001109 CYCLE 4 DAY 1 4 13MAR2019
JPN-00019-81001109 CYCLE 5 DAY 1 5 04APR2019
JPN-00019-81001109 CYCLE 6 DAY 1 6 25APR2019
;
run;
Thanks,
Adithya
Do you mean something like this:
PROC SQL;
CREATE TABLE EX_with_TR AS
SELECT EX.subjid
,COALESCE(TR.Visit, EX.Visit) AS Visit
,Ex.cycle
,Ex.exstdtc
FROM EX
LEFT JOIN TR
ON EX.subjid = TR.subjid
;
QUIT;
This code will give you the "Visit" information from TR if TR is populated for the SubjID, but you will still have all the rows from EX even if there is no match in TR. Is that what you need?
The first 27 rows of the results are below.
Jim
Do you mean something like this:
PROC SQL;
CREATE TABLE EX_with_TR AS
SELECT EX.subjid
,COALESCE(TR.Visit, EX.Visit) AS Visit
,Ex.cycle
,Ex.exstdtc
FROM EX
LEFT JOIN TR
ON EX.subjid = TR.subjid
;
QUIT;
This code will give you the "Visit" information from TR if TR is populated for the SubjID, but you will still have all the rows from EX even if there is no match in TR. Is that what you need?
The first 27 rows of the results are below.
Jim
Something like this, then?
PROC SQL;
CREATE TABLE EX_with_TR AS
SELECT EX.subjid
,COALESCE(TR.Visit, EX.Visit) AS Visit
,Ex.cycle
,COALESCE(TR.adt, Ex.exstdtc) AS adt
FROM EX
LEFT JOIN TR
ON EX.subjid = TR.subjid
;
QUIT;
Jim
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.