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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

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

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


Accepted Solutions
Solution
‎06-22-2017 08:12 AM
Super User
Posts: 6,948

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

[ Edited ]

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎06-22-2017 08:12 AM
Super User
Posts: 6,948

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

[ Edited ]

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 253

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

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;
Respected Advisor
Posts: 3,896

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

@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;
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 98 views
  • 1 like
  • 4 in conversation