Merge or concatenate the table

[Gick]

data T1;
   input mod $ var $ moy1 ;
   datalines;
   . AA 23 
   as AA 45
   sc AA 68
   . CC  0
   ic CC 1
   vi CC 35
   cm CC 235
   . DD 4
   fi DD 36
   dh DD 35
   sg DD 10
;
run; 

data T2;
   input mod $ var $ moy2;
   datalines;
   . AA 0
   as AA 10
   sc AA 23
   . CC 10
   ic CC 12
   vi CC 23
   cm CC 9
   . DD 3.3
   fi DD 21
   dh DD 16
   sg DD 13
;
run; 

data T3;
   input mod $ var $ moy3 ;
   datalines;
   . AA 12
   as AA 4
   sc AA 0
   . CC 11
   ic CC 35
   vi CC 6
   cm CC 8
   . DD 12
   fi DD 4
   dh DD 14
   sg DD 15
;
run; 



/* get the Matab table */
data MaTab;
   input mod $ var $ moy1 moy2 moy3 ;
   datalines;
   . AA 23 0 12
   as AA 45 10 4
   sc AA 68 23 0
   . CC  0 10 11
   ic CC 1 12 35
   vi CC 35 23 6
   cm CC 235 9 8
   . DD 4 3.3 12
   fi DD 36 21 4
   dh DD 35 16 14
   sg DD 10 13 15
;
run;

Hi,

I have several sas tables (T1,T2,T3,...,Tn). I am providing you with an extract (T1, T2 and T3). I want to create a code which allows me to have the Matab table whose structure is as follows: (see the table).

I tried to get it by concatenating or merging (merge with data step or sql with proc sql) without success. The result in screenshot is not good and this is what I get even though I want to have the Matab table.

Can anyone suggest me a code or an approach that can get the Matab table please.

Best regards,

Gick

[Patrick]

Below code works for your sample data. 

For your real data: Do you want a result set with observations that have matching keys {mod,var} in all tables (=inner join) or something else.

proc sql;
  create table want as
  select 
    t1.*,
    t2.moy2,
    t3.moy3
  from t1 

  inner join t2
    on t1.mod=t2.mod and t1.var=t2.var

  inner join t3
    on t1.mod=t3.mod and t1.var=t3.var

  order by mod, var;
  ;
quit;

proc sort data=MaTab;
  by mod var;
run;

proc compare base=MaTab comp=want;
run;

Or just for fun here two alternative data step options

data want;
  if _n_=1 then
    do;
      if 0 then set t2(keep=moy2) t3(keep=moy3);
      dcl hash h2(dataset:'t2');
      h2.defineKey('mod','var');
      h2.defineData('moy2');
      h2.defineDone();
      dcl hash h3(dataset:'t3');
      h3.defineKey('mod','var');
      h3.defineData('moy3');
      h3.defineDone();
    end;
    call missing(of _all_);
    set t3;
    if h2.find()=0 and h3.find()=0;
run;

proc sort data=t1 out=t_sorted1;
  by mod var;
run;
proc sort data=t2 out=t_sorted2;
  by mod var;
run;
proc sort data=t3 out=t_sorted3;
  by mod var;
run;

data want;
  merge t_sorted1(in=t1) t_sorted2(in=t2) t_sorted3(in=t3);
  by mod var;
  if t1 and t2 and t3;
run;

[Gick]

[Gick]

It works for this extracted data. But, when I apply it in my tables, the Mod mode is empty. The mods entered are not displayed and I don't know why?

I'll take a screenshot for you.

THANKS.

[Gick · Accepted solution]

In fact when there are more than 10 tables, the code does not take into account non-empty ones.

[Patrick]

---

@Gick wrote:
In fact when there are more than 10 tables, the code does not take into account non-empty ones.

---

Assuming the variables that make up the business key are {modalite, variable} - or {mod,var} in your sample data - then I guess in your 10 table case there was no combination of values for {modalit, variable} that existed in all tables (except for when modalite is missings). Because the sample code I've posted uses an INNER JOIN (similar logic for the data steps) only rows where the key values exist in all source tables will get selected.

Questions you need to answer:

1. Is {modalite, var} the business key in all your source tables meaning each value combination exists only once within a table?

2. What should happen if a key combination doesn't exist in all tables? Should the contributing variables from this table (not the key ones) just become missing?

    - means that you would never drop a row from any table to create the result table.

[Gick]

It's good. Just use "left join" instead of "Inner join".

Thank you so much

[Patrick]

---

@Gick wrote:
It's good. Just use "left join" instead of "Inner join".

Thank you so much

---

ONLY if your first table is the master and you don't need any rows from other tables that don't have a match to this first table.

Here the documentation for the joins you likely need: Example of a Full Outer Qualified Join

[Patrick]

---

@Gick wrote:
In fact when there are more than 10 tables, the code does not take into account non-empty ones.

---

@Gick That's not the solution and what you observe has NOTHING to do with the number of tables.

[Kurt_Bremser]

How does this screenshot relate to the example data you posted?

What are the key variables, and what does COUNT mean?

Keep in mind that missing values (like the blanks in modalite) will always appear first. You should consider changing the order of variables in the BY statements.