topic Re: PROC SQL LEFT JOIN HAPPEND BUT Most VALUE IS SHOWN in SAS Programming

PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

raheleh22 — Sat, 01 Jun 2024 18:30:08 GMT

Hi,

I am using proc sql left join to merge my two excels on based on two mutual columns they have.

here is the code I use:

proc sql;
CREATE TABLE FINALV3 AS SELECT * FROM WNPOP LEFT JOIN pop2012 ON WNPOP.State_Name_All=pop2012.State_Name_All AND WNPOP.County_Name_All=pop2012.County_Name_All;
QUIT;

while the proc happened and I can see the columns joined and the number of obs are correct, I do not have values in the joined columns. (THERE IS NO ERROR in the LOG ).

any ideas of what is going wrong?

Re: PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

Kurt_Bremser — Sat, 01 Jun 2024 19:52:52 GMT

Looks like no match was found.

Inspect your data for the values of your key variables. Exact spelling, upper/lowercase, leading blanks.

Also stop using the asterisk, it will cause at least WARNINGs for the key variables, and it might also be the cause of your problem if variables are present in both datasets.

Re: PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

raheleh22 — Sat, 01 Jun 2024 20:13:05 GMT

the mutual columns in these two tables are county_name_all and state_name_all and that is why i am doing the join based on these two. I have double checked and they are same in every thing in both excels. i even check and their excel format are same too. but still I have the problem

Re: PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

Kurt_Bremser — Sat, 01 Jun 2024 20:22:17 GMT

You constantly talk about "Excels", but this here are the SAS communities, where datasets are used.

Don't trust anything you see in Excel. Inspect the datasets after import in SAS, using SAS means, e.g. displaying character values with $HEX formats to reveal hidden characters and the like.

If a join "does not work", then the key values do not match, period.

Re: PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

Patrick — Sun, 02 Jun 2024 03:08:28 GMT

@raheleh22 The screenshot indicates that none of the rows from the "left" table joins with the "main" table.

Often such "issues" occur when there are leading blanks or especially differences in casing. Try if below code returns the desired result.

proc sql feedback;
  create table finalv3 as 
    select *
    from wnpop l
    left join pop2012 r 
      on    upcase(strip(wnpop.state_name_all)) =upcase(strip(pop2012.state_name_all))
        and upcase(strip(wnpop.county_name_all))=upcase(strip(pop2012.county_name_all))
  ;
quit;

Re: PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

ballardw — Sun, 02 Jun 2024 06:08:41 GMT

Without actual values from the data set we cannot tell what actually happens.

The two most common causes for text values not matching are case, such as ALL UPPER CASE letters not matching Some Upper Case letters. The other is presence of leading spaces in one or more of the variables.

Try

proc sql;
CREATE TABLE FINALV3 AS 
SELECT Wnpop.* , pop2012.*
FROM WNPOP LEFT JOIN pop2012 ON upcase(strip(WNPOP.State_Name_All))=upcase(strip(pop2012.State_Name_All)) AND upcase(strip(WNPOP.County_Name_All))=upcase(strip(pop2012.County_Name_All));
QUIT;

Specifically related to County names in different source files sometimes they will actually have the word "County" and sometimes not. So that is something to examine as well as "Ada" does not match "Ada County".

Re: PROC SQL LEFT JOIN HAPPEND BUT NO VALUE IS SHOWN

mariangela86 — Sun, 02 Jun 2024 21:34:07 GMT

The pop2012 fields used in the join have the suffix "_ALL": pop2012.Country_Name_All pop2012.State_Name_All , but in the picture, the table "finalv13" has pop2012 FIELDS, WITHOUT SUFFIX "_ALL"!

Correct the name of the two fields in the join:

pop2012.Country_Name
pop2012.State_NAME

Below an example code with alias for tables, with upcase(converts all lowercase letters to uppercase letters) and compress(remove blanks) functions for variables:

proc sql;
CREATE TABLE FINALV3 AS
SELECT
t1.* ,
t2.State_NAME,
t2.County_Name, t2.POP_2012
FROM WNPOP t1 LEFT JOIN pop2012 t2
ON
compress(upcase(t1.State_Name_All))=compress(upcase(t2.State_NAME)) AND
compress(upcase(t1.County_Name_All))=compress(upcase(t2.County_Name));
QUIT;

Re: PROC SQL LEFT JOIN HAPPEND BUT Most VALUE IS SHOWN

raheleh22 — Thu, 01 Aug 2024 16:33:08 GMT

I am having trouble to know why my syntax not working properly. I am using a left join to join two tables based on 2 mutual coloumn ( Fipscode & Datetime) I made sure these two columns formatting are same before merging and here is my syntax:

PROC SQL;
CREATE TABLE pamspi AS SELECT FROM PAMV1 LEFT JOIN spi12 ON pamV1.FIPScode=spi12.FIPScode AND pamv1.Datetime=spi12.Datetime;
QUIT;
proc print data=pamspi;
run;

PROC SQL;
CREATE TABLE pamspi AS SELECT * FROM PAMV1 LEFT JOIN spi12 ON pamV1.FIPScode=spi12.FIPScode AND pamv1.Datetime=spi12.Datetime;
QUIT;
proc print data=pamspi;
run;

and here is what happens:

I see out of 143 obs I was expecting to be merged only 4 rows values have been merged and all others are showing missing as above. any thoughts on this?

Re: PROC SQL LEFT JOIN HAPPEND BUT Most VALUE IS SHOWN

Kurt_Bremser — Thu, 01 Aug 2024 16:49:20 GMT

@raheleh22 wrote:

I am having trouble to know why my syntax not working properly. I am using a left join to join two tables based on 2 mutual coloumn ( Fipscode & Datetime) I made sure these two columns formatting are same before merging and here is my syntax:

PROC SQL;
CREATE TABLE pamspi AS SELECT FROM PAMV1 LEFT JOIN spi12 ON pamV1.FIPScode=spi12.FIPScode AND pamv1.Datetime=spi12.Datetime;
QUIT;
proc print data=pamspi;
run;
PROC SQL;
CREATE TABLE pamspi AS SELECT * FROM PAMV1 LEFT JOIN spi12 ON pamV1.FIPScode=spi12.FIPScode AND pamv1.Datetime=spi12.Datetime;
QUIT;
proc print data=pamspi;
run;
and here is what happens:

I see out of 143 obs I was expecting to be merged only 4 rows values have been merged and all others are showing missing as above. any thoughts on this?

Very simple. You have only 4 matches.

Maxim 3: Know Your Data. This includes all data types, formats, and real values.

Are values left- or right-aligned (leading blanks!)?

Do such codes have leading zeroes in one dataset, but not the other (could be the result if codes were stored as numbers along the way)?