Embed an if/then (Case) statement within an SQL join

[brulard]

hi

I'm joining two tables, where I'd like the content from var in column b to be translated into a text of my choice. Instead of my creating another step, can this be done in a single query?

Example code below.

In the final output, I'd like my variable "relationship" to read "Primary" if "0", or "Secondary" if "2".

Data table1;
Input ID $ Name $;
Datalines;
001 John
004 Marc
007 Jess
012 Peter
Run;

Data table2;
Input ID $ Relationship $ ;
Datalines;
001 0
004 2
007 0
012 2
Run;

Proc sql; create table sofar as
Select a.*,b.relationship
From table1 a,table2 b
Where a.id=b.id;quit;

Thanks

[Kurt_Bremser · Accepted solution]

Use the case construct:

proc sql;
create table sofar as
  select
    a.*,
    case
      when b.relationship = '0' then 'Primary'
      when b.relationship = '2' then 'Secondary'
      else 'Undefined'
    end as relationship
  from table1 a,table2 b
  where a.id=b.id
;
quit;

or create a value format and assign it to relationship:

proc format library=work;
value $rel
  '0' = 'Primary'
  '2' = 'Secondary'
  other = 'Undefined'
;
run;

proc sql;
create table sofar2 as
  select
    a.*,
    b.relationship format = $rel.
  from table1 a,table2 b
  where a.id=b.id
;
quit;

[kiranv_]

something like this

Proc sql; create table sofar as
Select a.*, 
      case when b.relationship ="0" then "Primary"
           when b.relationship ="2" then "Secondary"
	  else 'missing'
	  end as relationship
From table1 a,table2 b
Where a.id=b.id;
quit;

[Patrick]

@brulard

Using a format is another option.

Data table1;
  Input ID $ Name $;
  Datalines;
001 John
004 Marc
007 Jess
012 Peter
Run;

Data table2;
  Input ID $ Relationship $;
  Datalines;
001 0
004 2
007 0
012 2
Run;

proc format;
  value $relationship(default=11)
    0 = 'Primary'
    1 = 'Secondary'
    other='not defined'
    ;
quit;

Proc sql;
  create table sofar as
    Select a.*, put(b.relationship,$relationship.) as Relationship_Desc
      From table1 a,table2 b
        Where a.id=b.id;
quit;