BookmarkSubscribeRSS Feed
manya92
Fluorite | Level 6

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 OrderIDAdministeredDateOrderedAmount .
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
4 REPLIES 4
ybolduc
Quartz | Level 8

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

 

Thise is likely caused by missing values.

manya92
Fluorite | Level 6
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
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 827 views
  • 0 likes
  • 4 in conversation