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

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1812 views
  • 1 like
  • 6 in conversation