topic Re: How to join two tables, by a variable with the same column names ? in SAS Programming

How to join two tables, by a variable with the same column names ?

KarimaTouati — Tue, 21 Sep 2021 13:39:26 GMT

Hello folks !

I've got a problem, which I barely able to solve.

I have to make a table C based on another table A and B where the two tables have the same column names but for different years (for example 2018 and 2019). I have already a column to flag the year in the first table A.

When making a left join (with proc sql) by the given ID while keeping the same variable names on the second table B (where I have data only for year 2019), I got null values for the year 2019 in the table C.

I don't want to change the variable names since I want to fill the table C in the same row and not have new columns for the year 2019.

For example there is a "A" table with a,b,c,d,Year columns and there is an another "B" table with same columns, but different datas. I want to merge them by 'D' in the "C" - new table - so it should look like this: a,b,c, D (this is the key),Year.

But as I use left join I got null data from the table B.

I want to mention that I want to fill my data fo the same row.

How should I join them? Any ideas?

Here is the full code:

proc sql;
create table Base_Sinistre_2 as select A.*,
put(B.Annee,8.) as ANNEE_prod,
B.Contrat,
B.Agence,
B.usagee
From Base_Sinistre_1 as A
left join production122019_nnd as B On A.Num_POLICE=B.Contrat and put(A.Survenance,8.)=put(B.Annee,8.)
Where B.Code_Produit not in (580,595) ;
Quit;

And here is the result.

I will do the same for the table on which I have data for 2020.

Re: How to join two tables, by a variable with the same column names ?

Kurt_Bremser — Tue, 21 Sep 2021 11:46:46 GMT

When you get only missing values for the B. variables, then you do not have matches.

In particular, I would inspect these columns:

A.Survenance_sin=put(B.Annee,10.)

Could it be that A.Survenance_sin has leading zeroes, or a different length overall?

Also note that

put(B.Annee,10.)

will right-adjust the string, so the number 1 would end up as

(9 leading blanks)

Re: How to join two tables, by a variable with the same column names ?

KarimaTouati — Tue, 21 Sep 2021 12:32:31 GMT

I checked the length of both variables but it was not the problem

I have edited my question and added the output table for overview.

Re: How to join two tables, by a variable with the same column names ?

Tom — Tue, 21 Sep 2021 12:44:50 GMT

This does not make much sense

put(A.Survenance,8.)=put(B.Annee,8.)

If those two variables are numeric why not just compare them directly?

A.Survenance=B.Annee

Re: How to join two tables, by a variable with the same column names ?

KarimaTouati — Tue, 21 Sep 2021 12:53:42 GMT

Even if I do so, that doesn't solve the problem.

Re: How to join two tables, by a variable with the same column names ?

Tom — Tue, 21 Sep 2021 13:02:14 GMT

I do not understand what the question is.

From your photograph it looks like whatever table provided those columns on the right only has data for YEAR=2018.

What is it that you want to happen differently?

Note: Please post data as text not photographs. Please use the Insert Code and/or Insert SAS Code button for posting data and/or program code.

Re: How to join two tables, by a variable with the same column names ?

KarimaTouati — Tue, 21 Sep 2021 13:47:12 GMT

I want to fill rows where Year=2019 with data from "production122019_nnd".

When I want to combine my original table "Base_Sinistre_1" with "production122019_nnd" which includes only data for the year 2019 to complete the rows of "Base_Sinistre_1" where Survenance=2019 I got null data with this warning message :

WARNING: Variable ANNEE_prod already exists on file WORK.BASE_SINISTRE_2.
WARNING: Variable CONTRAT already exists on file WORK.BASE_SINISTRE_2.
WARNING: Variable AGENCE already exists on file WORK.BASE_SINISTRE_2.
WARNING: Variable USAGEE already exists on file WORK.BASE_SINISTRE_2.

Re: How to join two tables, by a variable with the same column names ?

Tom — Tue, 21 Sep 2021 13:59:05 GMT

So you cannot have two variables with the same name.

SQL code will keep the first one it sees and drop the second one.

It is not clear how you want to handle those columns when there is a match on the keys.

Do you always want the value from the second table instead of the first? Just only include the variable from the second table in the list of columns.

Do you want to use the value from the second table only if it is not missing? Use coalesce:

 ,coalesce(B.ANNEE_prod,A.ANNEE_prod) as  ANNEE_prod

Do you want to use the value from the second table even if it is missing, but only when the join was successful? Then perhaps use CASE statement

case when (not missing(B.keyvar) then B.ANNEE_prod else A.ANNEE_prod end as  ANNEE_prod

Detecting when a table is contributing to a join can be tricky in SQL syntax.

Re: How to join two tables, by a variable with the same column names ?

ballardw — Tue, 21 Sep 2021 14:38:34 GMT

INPUT data in the form of a data step will allow us to test code. We can't code against pictures. Displaying incorrect/ undesired results does not show what the desired result is.

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the </> icon or attached as text to show exactly what you have and that we can test code against.