Proc Sql Left Join - joining vars special condition

[Nikos]

Hi all,

I have the following two datasets

   DATA Set 1                               DATA Set 2

VAR1     VAR2                     L_VAR1      L_VAR2     VALVAR

A                                             A                             RED

B                                             B                             GREEN

C                                             C                             BROWN

D                                             D                             YELLOW

Z                                              Z                             WHITE

Z              ZA                            Z              ZA           GREEN

Z              ZB                            Z              ZB           PURPLE

Z              ZC                                                          

Z              ZD                                                          

Z              ZE                                                          

I want the following OUTPUT

OUTPUT DATA Set 3                      

VAR1     VAR2     VALVAR

A                             RED

B                             GREEN

C                             BROWN

D                             YELLOW

Z                             WHITE

Z              ZA           GREEN

Z              ZB           PURPLE

Z              ZC           WHITE

Z              ZD           WHITE

Z                   ZE               WHITE

I would like to use Proc Sql with a Left Join but I had not been succesful.

I would very much appreciate any suggestions

Thank you

Kind regards

Nikos

[Ksharp]

How about:

data a;
input var1 $ var2 $;
cards;
A               .
B              .
C             .  
D              .
Z              . 
Z              ZA 
Z              ZB 
Z              ZC
Z              ZD 
Z              ZE  
;
run;
data b;
input (l_var1 l_var2 valvar) ($);
cards;
A .        RED
B .        GREEN
C .        BROWN
D .        YELLOW
Z .        WHITE
Z ZA           GREEN
Z ZB           PURPLE
;
run;

proc sql;
create table want as
 select a.*,coalescec(b.valvar,(select valvar from b where l_var1='Z' and l_var2 is missing)) as valvar
  from a left join b on a.var1=b.l_var1 and a.var2=l_var2;
quit;

Ksharp

[Nikos]

Hi Ksharp,

Thank you for your solution it is the answer to my problem as I initially stated.

Unfortunately I mistated my problem, my question is as follows:

data a;

input var1 $ var2 $;

cards;

A       .

B       .

C       .

D       .

Z       .

Z       ZA

Z       ZB

Z       ZC

Z       ZD

Z       ZE 

;

run;

data b;

input (l_var1 l_var2 valvar_1 valvar_2) ($);

cards;

A      .    TOT_RED      RED

B      .    TOT_GREEN    GREEN

C      .    TOT_BROWN    BROWN

D      .    TOT_YELLOW   YELLOW

Z      ZA   TOT_WHITE    VIOLET

Z      ZB   TOT_WHITE    PURPLE

;

run;

In Data a I have single Z's while in Data b I do not.

data WANT;

input (var1 var2 valvar_1 valvar_2) ($);

cards;

A       .  TOT_RED      RED

B       .  TOT_GREEN    GREEN

C       .  TOT_BROWN    BROWN

D       .  TOT_YELLOW   YELLOW

Z      .   TOT_WHITE   

Z      ZA  TOT_WHITE    VIOLET

Z      ZB  TOT_WHITE    PURPLE

Z      ZC  TOT_WHITE   

Z      ZD  TOT_WHITE   

Z      ZE  TOT_WHITE   

;

run;

Any suggestions would be more than welcomed

Thank you in advance

Kind regards

Nikos

[Haikuo]

Since it is 2:30am for Ksharp, and it only takes minor tweak on his original code,  I will just answer that for him.

proc sql;

create table want as

select a.*,coalescec(b.valvar_1,(select valvar_1 from b where l_var1='Z' and l_var2 = 'ZA')) as valvar_1, valvar_2

  from a left join b on a.var1=b.l_var1 and a.var2=l_var2

;

quit;

Good Luck!

Haikuo

[Hima · Accepted solution]

Code:

data a;
input var1 $ var2 $;
cards;
A               .
B              .
C             . 
D              .
Z              .
Z              ZA
Z              ZB
Z              ZC
Z              ZD
Z              ZE 
;
run;
data b;
input (l_var1 l_var2 valvar) ($);
cards;
A .        RED
B .        GREEN
C .        BROWN
D .        YELLOW
Z .        WHITE
Z ZA        GREEN
Z ZB        PURPLE
Z ZC  WHITE
Z ZD  WHITE
Z ZE  WHITE
;
run;

proc sql;
select a.var1, a.var2, b.valvar from a inner join b on a.var2 =b.l_var2 and a.var1=b.l_var1;
quit;

Output:

                                  var1      var2      valvar

                                  ƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒƒ

                                  A                   RED

                                  B                   GREEN

                                  C                   BROWN

                                  D                   YELLOW

                                  Z                   WHITE

                                  Z         ZA        GREEN

                                  Z         ZB        PURPLE

                                  Z         ZC        WHITE

                                  Z         ZD        WHITE

                                  Z         ZE        WHITE