BookmarkSubscribeRSS Feed
kajal_30
Quartz | Level 8

 

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

 

I need help converting this into proc sql

 

thanks

 

7 REPLIES 7
Reeza
Super User

Switch the IF/THEN statements to CASE statements. If you're having difficulties, post the code you've tried.

This assumes a one to one or one to many join, not a many to many join. If it's a many to many join, you cannot replicate it via SQL.

 


@kajal_30 wrote:

 

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

 

I need help converting this into proc sql

 

thanks

 


 

kajal_30
Quartz | Level 8

data merge (drop = w);

merge x (in = a rename = ( new = old)) 

y (in= b) ;

by t ;

if a and b then d = "e" ;

if a and not b then d = "f" ;

if b and not a then d = "g" ;

run;

 

I need help converting this into proc sql under case when how to handle in = a and in = b

 

thanks

PeterClemmensen
Tourmaline | Level 20

Why do you want to do this in SQL? The data step seems to be a fine tool to me?

VRKiwi
Obsidian | Level 7

Something like this?

data X ;
input T NEW $;
cards;
1 a
2 b
run;
data Y;
input T NEW $;
cards;
1 c
3 d
run;

proc sql;
  create table WANT as 
    select X.NEW as OLD
         , Y.NEW
         , coalesce (X.T, Y.T) as T
         , case when X.T is null then "g"
                when Y.T is null then "f"
                else                  "e" end as D             
    from WORK.X 
           full outer join
         WORK.Y
           on X.T = Y.T;     
quit; 
    

 

OLDNEWTD
ac1e
b 2f
 d3g

 

 

Patrick
Opal | Level 21

@kajal_30 

Using a SQL Full Join you will only get the same result than with the merge if your variable T is the key in the tables and the relationship between the tables is not many:many.

 

You can use a CASE statement to work out which table contributes to the result set. If a table doesn't contribute then the value of t from this table will be missing.

   select 
      ....
      x.new as old,
      case
        when x.t=y.t then 'e'
        when missing(y.t) then 'f'
        when missing*x.t) then 'g'
        else ' '
        end as d,
      ....
    from x full join y
      on x.t=y.t

 

 

 

VRKiwi
Obsidian | Level 7

@Patrick wrote:

Using a SQL Full Join you will only get the same result than with the merge if your variable T is the key in the tables and the relationship between the tables is not many:many.


 

... and also if the key (T here) doesn't have a missing value in any table.

 

 

 

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 7 replies
  • 1762 views
  • 1 like
  • 6 in conversation