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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.