POPPKPD

[chinna0369]

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

[jimbarbour · Accepted solution]

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

[chinna0369]

Yes, and take closedt exstdt to adt. Thanks!

[jimbarbour]

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