topic full join proc sql in SAS Programming

full join proc sql

manya92 — Fri, 24 Aug 2018 14:17:28 GMT

Hi,

I am trying to use full join to join two tables but I am unable to see certain variables. They are coming as blank. There's no error or warning but the variables are printing with blank values.

proc sql ;
	create table admn_order as 
	select distinct a.PatientID,a.OrderID,a.AdministeredDate,a.AdministeredAmount,b.ExpectedStartDate,b.OrderedAmount
	from dfi_panc.medicationadmin as a 
	full join dfi_panc.medicationorder as b 
	on a.PatientID=b.PatientID and a.OrderID=b.OrderID ;
quit;

Here's a snapshot of the output ;

PatientID

OrderID

AdministeredDate

OrderedAmount

0.2

0.25

0.3

0.4

0.5

0.6

0.8

Re: full join proc sql

ybolduc — Fri, 24 Aug 2018 14:30:51 GMT

Can you please provide a copy of your log and the datasets your are using?

Thise is likely caused by missing values.

Re: full join proc sql

manya92 — Fri, 24 Aug 2018 14:34:14 GMT

proc sort data = dfi_panc.medicationadmin out =admin_s (keep=PatientID OrderID AdministeredDate AdministeredAmount) nodupkey ;
	by PatientID AdministeredDate OrderID; 
run ;

proc sort data = dfi_panc.medicationorder out =order_s (keep=PatientID OrderID ExpectedStartDate OrderedAmount) nodupkey ;
	by PatientID ExpectedStartDate OrderID; 
run ;

data admin_order;
	merge order_s admin_s; 
	by PatientID OrderID ;
run ;

I am using this code now but it is saying this :

ERROR: BY variables are not properly sorted on data set WORK.ORDER_S.

Re: full join proc sql

RW9 — Fri, 24 Aug 2018 14:49:06 GMT

You are sorting on:

	by PatientID ExpectedStartDate OrderID;

But you are merging on:

	by PatientID OrderID ;

So you can see what the difference is. Note we cannot debug your code as we have no data to work with (post test data in the form of a datastep:
https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

Re: full join proc sql

ballardw — Fri, 24 Aug 2018 15:30:22 GMT

@manya92 wrote:

Hi,

I am trying to use full join to join two tables but I am unable to see certain variables. They are coming as blank. There's no error or warning but the variables are printing with blank values.
proc sql ;
	create table admn_order as 
	select distinct a.PatientID,a.OrderID,a.AdministeredDate,a.AdministeredAmount,b.ExpectedStartDate,b.OrderedAmount
	from dfi_panc.medicationadmin as a 
	full join dfi_panc.medicationorder as b 
	on a.PatientID=b.PatientID and a.OrderID=b.OrderID ;
quit;
Here's a snapshot of the output ;

PatientID OrderID AdministeredDate OrderedAmount .

2 . . . 0

3 . . . 0.2

4 . . . 0.25

5 . . . 0.3

6 . . . 0.4

7 . . . 0.5

8 . . . 0.6

9 . . . 0.8

10 . . . 1

Is PatientId missing for ALL records or only some? A full join where there is no actual matching value for one or more of the JOIN ON values still includes the records but the value may be missing.

Perhaps instead of

select distinct a.PatientID

you actually want:

select distinct coalesce (a.PatientID,b.PatientId), <etc>

untested but should have the B.patiendid when missing A.patientid for the particular record.

You may need to do the same with the OrderID variable as well.