CODEWITHSUNDEEP
joinsasnatural-join
user11160288
user11160288

Reputation: 1

Joining two tables based on matching two columns

I'm trying to join two tables: Table A has three columns: State, County, and Count (of Farmer's Markets in said county) Table B has several columns: State, County, and several data columns (like food access score)

I'm trying to combine them in such a way as to put the Count for each State/County combination (since there are multiple counties with the same name) together with the State and County and data columns from Table B.

I've been banging my head on SAS, trying to get a join to cooperate. I read a few other questions on here, but I can't find where the mistake is in my code. 

PROC SQL; 
CREATE TABLE WORK.QUERY1 
AS 
SELECT FMDV4.State, FMDV4.County, FMDV4.Count, CFSDV1.GROC14, 
CFSDV1.SUPERC14, CFSDV1.CONVS14, CFSDV1.SPECS14, CFSDV1.FOODINSEC_13_15, 
CFSDV1.PCT_LACCESS_POP15, CFSDV1.DIRSALES_FARMS12, CFSDV1.FMRKT16, 
CFSDV1.FOODHUB16, CFSDV1.CSA12, CFSDV1.POVRATE15, CFSDV1.PERPOV10 
    FROM FNLPRJT.CFSDV1 AS CFSDV1
    INNER JOIN FNLPRJT.FMDV4 AS FMDV4 
    ON (( CFSDV1.State = FMDV4.State ) AND ( CFSDV1.County = 
FMDV4.County )); 
QUIT;

I also tried a few variants, like:

PROC SQL; 
CREATE TABLE WORK.QUERY1 
AS 
SELECT FMDV4.State, FMDV4.County, FMDV4.Count, CFSDV1.GROC14, 
CFSDV1.SUPERC14, CFSDV1.CONVS14, CFSDV1.SPECS14, CFSDV1.FOODINSEC_13_15, 
CFSDV1.PCT_LACCESS_POP15, CFSDV1.DIRSALES_FARMS12, CFSDV1.FMRKT16, 
CFSDV1.FOODHUB16, CFSDV1.CSA12, CFSDV1.POVRATE15, CFSDV1.PERPOV10 
    FROM FNLPRJT.CFSDV1 AS CFSDV1
    INNER JOIN FNLPRJT.FMDV4 AS FMDV4 
    ON CFSDV1.State = FMDV4.State 
    WHERE CFSDV1.County = FMDV4.County; 
QUIT;

I get a table of 0 rows with the columns as they should be (State, County, Count, ). I'm just missing the dang data! Can anyone please help me find my mistake?

Upvotes: 0

Views: 2182

Answers (1)

user2239912
user2239912

Reputation: 11

Can you try 

propcase(CFSDV1.State) = propcase(FMDV4.State)

and 

propcase(CFSDV1.County) = propcase(FMDV4.County);

If this doesn't work try character functions like trim and compress to remove any blanks that might be present in the data.

Upvotes: 0

Related Questions