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

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

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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_
Rhodochrosite | Level 12

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
Opal | Level 21

@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;

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!

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.

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
  • 3 replies
  • 1427 views
  • 1 like
  • 4 in conversation