Dear all,
Assume there are two datasets as follow:
data one;
input A B;
cards;
4 6
3 2
run;
data two;
input C D;
cards;
1 3
2 5
3 8
4 7
run;
As you see neither variables nor obs are equal.
I would like to create another variable E in dataset one which is equal to var D where A=C. Finally, I am looking for a dataset for example like this:
A B C D E
4 6 . . 7
3 2 . . 8
. . 1 3 .
. . 2 5 .
. . 3 8 .
. . 4 7 .
So I can do some analyses on A and E.
Please let me know if I can use SAS for this reason.
Thank you!
OK. Add one more LEFT JOIN .
data one;
input A B;
cards;
4 6
3 2
;
run;
data two;
input C D;
cards;
1 3
2 5
3 8
4 7
5 9
6 10
;
run;
proc sql;
create table want as
select a.*,b.d as e,c.d as f
from (
select * from one
outer union
select * from two) as a
left join two as b on a.a=b.c
left join two as c on a.b=c.c
;
quit;
What if multiple values in C equals A?
What is the logic then?
data one;
input A B;
cards;
4 6
3 2
;
run;
data two;
input C D;
cards;
1 3
2 5
3 8
4 7
;
run;
proc sql;
create table want as
select a.*,b.d as e
from (
select * from one
outer union
select * from two) as a
left join two as b on a.a=b.c
;
quit;
OK. Add one more LEFT JOIN .
data one;
input A B;
cards;
4 6
3 2
;
run;
data two;
input C D;
cards;
1 3
2 5
3 8
4 7
5 9
6 10
;
run;
proc sql;
create table want as
select a.*,b.d as e,c.d as f
from (
select * from one
outer union
select * from two) as a
left join two as b on a.a=b.c
left join two as c on a.b=c.c
;
quit;
This gets exactly what you want:
proc sql;
create table want as select one.a, one.b, "" as c, "" as d, two.d as e
from one inner join two
on one.a = two.c
union all corr
select "" as a, "" as b, two.c, two.d, "" as e
from two
;
quit;
but I am not sure if you actually need this.
So table two is a lookup for values in table one; create a format from it, and use that later:
data one;
input A B;
cards;
4 6
3 2
;
data two;
input C D;
cards;
1 3
2 5
3 8
4 7
5 9
6 10
;
data lookup;
set two;
fmtname = "lookup";
type = "i";
rename
c=start
d=label
;
run;
proc format cntlin=lookup;
run;
data want;
retain a b c d e f;
call missing(of _all_);
/* these two statements are purely for setting variable order */
set
one (in=one)
two
;
if one
then do;
e = input(put(a,best.),lookup.);
f = input(put(b,best.),lookup.);
end;
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.