Join tables

Reply
Contributor
Posts: 30

Join tables

I need to join twi tables, the columns in table 1 changes all the time and I need to get always all the columns from table 1 and the ones from table to that are not in table 1.

The code I did is the following, but when the columns that are in Table 1 changes the code does not work.

PROC SQL;

   CREATE TABLE WORK.xxx(label="QUERY_FOR_CONTABILIDAD") AS

   SELECT t1.xxx1,

          t1.xxx2,

            t1.xxx3,

          t2.xxx11,

   t2.xxx4

       

      FROM WORK.QUERY_FOR_CONTABILIDAD_0001 t1

           INNER JOIN WORK.COPIA_DE_LISTADO_DE_CUENTAS_CONT t2 ON (t1.Cuenta_contable = t2.CUENTA);

  

QUIT;

When I do the project and columns in table 1 are different then I need to get xxx1 from t2 and not from t1.

PROC SQL;

   CREATE TABLE WORK.xxx(label="QUERY_FOR_CONTABILIDAD") AS

   SELECT t1.xxx1,

          t1.xxx2,

            t1.xxx3,

          t2.xxx11,

   t2.xxx4

       

      FROM WORK.QUERY_FOR_CONTABILIDAD_0001 t1

           INNER JOIN WORK.COPIA_DE_LISTADO_DE_CUENTAS_CONT t2 ON (t1.Cuenta_contable = t2.CUENTA);

  

QUIT;

I hope this is clear.

Thanks!

Super Contributor
Posts: 578

Re: Join tables

how do you know when to use t2.xxx1 and when to use t1.xxx1?

Contributor
Posts: 30

Re: Join tables

I always need to get it from t1. If xxx1 is not in t1 then I need to use t2.xxx1.

Super Contributor
Posts: 578

Re: Join tables

I'm sorry but I'm not following.  Can you give me some sample data?

Super User
Posts: 17,828

Re: Join tables

Can you use a data step merge instead, and use the assumption that the t2 will overwrite t1, if t1 is not present.

Is it possible for a column to be in both tables? 

Contributor
Posts: 30

Re: Join tables

Most columns are in both tables.

How can I do to keep all the variables of t1. and include the ones that are not in t1. from t2?

Respected Advisor
Posts: 4,649

Re: Join tables

Here is a not so elegant way to do what you want:

data a;

do x = "A","B","C"; y + 1; output; end; run;

data b;

do x = "B","C","D"; z + 1; output; end; run;

proc sql;

create table aname as

select memname, name from dictionary.columns where MEMNAME="A" and LIBNAME="WORK";

select catx(".", memname, name) into :varList separated by ","

from

  (select memname, name from aname

  union all

  select memname, name from dictionary.columns where MEMNAME="B" and LIBNAME="WORK" and

  name not in (select name from aname));

create table xxx as

select &varList from a inner join b on a.x=b.x;

drop table aname;

select * from xxx;

quit;

PG

PG
Super User
Posts: 17,828

Re: Join tables

proc sort data=table1; by mergefield; run;

proc sort data=table2; by mergefield; run;

data want;

merge table2 (in=a) table1(in=b);

if a and b;

run;

OR

data want;

merge table1(in=b) table2(in=a);

if a and b;

run;

Check which one meets your requirements, if one does Smiley Happy

If you want to restrict to certain fields you can a keep statement as appropriate.

Ask a Question
Discussion stats
  • 7 replies
  • 563 views
  • 0 likes
  • 4 in conversation