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