BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nikos
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
Hima
Obsidian | Level 7

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

View solution in original post

4 REPLIES 4
Ksharp
Super User

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
Fluorite | Level 6

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
Onyx | Level 15

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
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 932 views
  • 6 likes
  • 4 in conversation