full join proc sql

[manya92]

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

[ybolduc]

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

Thise is likely caused by missing values.

[manya92]

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.

[RW9]

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-dat...

[ballardw]

---

@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.