BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
dbcrow
Obsidian | Level 7

I'm trying to join two databases. Some rows in the left database have corresponding rows in the right database, but the output database doesn't show them. (Note: I've tried to reproduce this error with a reproducible example, but can't).  

 

Here's a few rows of the left database:  

left_db.png

Here are some rows from the right table:  

right_db.png

Here's the code I'm using to join them: 

	/*Add County to Overlap Table*/
	proc sql;
		create table overlap_all as
		select distinct a.*,
		b.patcounty as county
	from overlap_long a
		left join opioids_loc b
			on a.patientid = b.patientid
			and a.monyear = b.monyear;
	quit;

And here are some rows from the output table:  

join_db.png

Some of the values aren't showing up. For example, Row 10 in the output table (corresponding to "monyear" = 7-2021, "patientid" = 5472) should read "HUMBOLDT", but doesn't. 

 

Does anybody have any idea of what's going on? The weird part is that some rows that should show up, but others don't. 

 

Thanks,

David

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

Hard to tell but if MONYEAR is a character column and values aren't left justified then that might explain why some aren't matching. Try this to see if this could be happening:

proc sql;
		create table overlap_all as
		select distinct a.*,
		b.patcounty as county
	from overlap_long a
		left join opioids_loc b
			on a.patientid = b.patientid
			and left(a.monyear) = left(b.monyear);
	quit;

 

View solution in original post

4 REPLIES 4
Tom
Super User Tom
Super User

It is very hard to tell from photographs what values are in your datasets.  Are those little red icons that look kind of like the letter A supposed to indicate that the MONYEAR variables are CHARACTER variables?

 

Leading spaces are significant in comparing character values.  So '7-2011' and ' 7-2011' might look the same in a photograph but will not actually match.  Also if one of them has an  endash or emdash character instead of a hyphen then they will not match.

dbcrow
Obsidian | Level 7

Yes, "monyear" is a character variable. The dashes are all n-dashes, but now that I look closely, only values "10-2021", "11-2021", and "12-2021" have he county values that should be there. 

 

There's clearly a mismatch--maybe leading spaces--between monyear in the two datasets. Let my try trimming the leading values or formatting length of "monyear" in the left and right datasets so that they're the same. 

 

Any other suggestions? 

SASKiwi
PROC Star

Hard to tell but if MONYEAR is a character column and values aren't left justified then that might explain why some aren't matching. Try this to see if this could be happening:

proc sql;
		create table overlap_all as
		select distinct a.*,
		b.patcounty as county
	from overlap_long a
		left join opioids_loc b
			on a.patientid = b.patientid
			and left(a.monyear) = left(b.monyear);
	quit;

 

dbcrow
Obsidian | Level 7

Worked like a charm. Thanks, yet again!, SASKiwi, and to Tom, for both of your help. 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

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
  • 596 views
  • 3 likes
  • 3 in conversation