topic Re: observations discrepency in SAS Programming

observations discrepency

manya92 — Tue, 14 Aug 2018 19:56:39 GMT

Hi all,

I am trying to add a variable called height to my cohort dataset by left joining. I have 92 observations in my cohort so I am expecting the final number to be 92 after the join since I am only left joining (adding variables to the existing conditions). I am including both scenarios where height is missing and not missing. I am only getting 64 observations. I only want to see the most recent record of height 30 days prior to the index date. Attached is my code.

Please let me know how can I solve this.


*height most recent height within 30 days prior to the index date;
proc sql ;
	create table _02a_ht as 
	select a.*, b.labcomponent, b.TestResultCleaned,b.TestDate,
	case 
		when (a.index_date-30) le b.TestDate le a.index_date  and b.labcomponent ='Body Height' and b.TestResultCleaned ne .
		then b.TestResultCleaned 
		else .
	end as height	
	from _02a_ecog2 as a
	inner join dfi_panc.vitals as b 
	on a.PatientID=b.PatientID and b.labcomponent ='Body Height'
	order by a.PatientID,b.TestDate;
quit ;/*3463 */

*making sure the testdate is 30 days prior the index date;
data _02a_testdate;	
	set _02a_ht ;
	by PatientID TestDate ;
	if last.PatientID;
	where height = . or height ne .;
run ;/*64 */

There's no error

Re: observations discrepency

Reeza — Tue, 14 Aug 2018 20:09:36 GMT

I am trying to add a variable called height to my cohort dataset by left joining.

*height most recent height within 30 days prior to the index date;
proc sql ;
	create table _02a_ht as 
	select a.*, b.labcomponent, b.TestResultCleaned,b.TestDate,
	case 
		when (a.index_date-30) le b.TestDate le a.index_date  and b.labcomponent ='Body Height' and b.TestResultCleaned ne .
		then b.TestResultCleaned 
		else .
	end as height	
	from _02a_ecog2 as a
	inner join dfi_panc.vitals as b 
	on a.PatientID=b.PatientID and b.labcomponent ='Body Height'
	order by a.PatientID,b.TestDate;
quit ;/*3463 */

*making sure the testdate is 30 days prior the index date;
data _02a_testdate;	
	set _02a_ht ;
	by PatientID TestDate ;
	if last.PatientID;
	where height = . or height ne .;
run ;/*64 */

Try changing inner join to left join?

Re: observations discrepency

ballardw — Tue, 14 Aug 2018 20:14:03 GMT

While you may start with 92 in your cohort this bit of code:

inner join dfi_panc.vitals as b
on a.PatientID=b.PatientID and b.labcomponent ='Body Height'

has the potential to remove patientid values either because it does not appear in both data sets OR the only match in set b does not have "Body Height" as the value for labcomponent. Possibly due to spelling differences.

By definition from the online documentation:

An inner join returns only the subset of rows from the first table that matches rows from the second table.

Re: observations discrepency

manya92 — Tue, 14 Aug 2018 20:17:56 GMT

I changed that to a left join but now i have 89 obsv still not 92

Re: observations discrepency

Reeza — Tue, 14 Aug 2018 20:18:52 GMT

WHERE filters out the data. If you remove that do you get the records you want?

Look at the missing records and determine what condition is excluding them....with less than a 100 records you can manually verify it.

@manya92 wrote:

I changed that to a left join but now i have 89 obsv still not 92

Re: observations discrepency

Reeza — Tue, 14 Aug 2018 20:19:21 GMT

Sorry, not the WHERE but this likely filters out the rest: and b.labcomponent ='Body Height'

Re: observations discrepency

manya92 — Tue, 14 Aug 2018 20:22:39 GMT

Okay, now i have 89 obvs , i want to see which obsv from the prev dataset haev been deleted ? how do i see that?