topic Proc Sql Left Join - joining vars special condition in SAS Procedures

Proc Sql Left Join - joining vars special condition

Nikos — Thu, 16 Feb 2012 05:22:13 GMT

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

Proc Sql Left Join - joining vars special condition

Ksharp — Thu, 16 Feb 2012 10:07:10 GMT

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

Proc Sql Left Join - joining vars special condition

Hima — Thu, 16 Feb 2012 14:12:45 GMT

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

Proc Sql Left Join - joining vars special condition

Nikos — Thu, 16 Feb 2012 19:22:35 GMT

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

Proc Sql Left Join - joining vars special condition

Haikuo — Thu, 16 Feb 2012 19:38:58 GMT

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