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