Creating a new variable using two dataset with different var names and observations

[mghamari63]

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!

[PeterClemmensen]

What if multiple values in C equals A?

[mghamari63]

Thanks!

Yes.
There are multiple equal values.
That's another problem I have.

[PeterClemmensen]

What is the logic then?

[mghamari63]

Well, I want to create E according to D for every A that equals to C
I know it does not work but something like this:
if A=C then E=D;

[Ksharp]

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;

[mghamari63]

Thank you so much Ksharp!
It works like a charm.
What if I want the same thing apples for variable F for every B that equals to again C?
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;
Let say I want to create a table like this:
A B C D E F
4 6 . . 7 10
3 2 . . 8 5
. . 1 3 . .
. . 2 5 . .
. . 3 8 . .
. . 4 7 . .
. . 6 10 . .

Thank you!

[Ksharp · Accepted solution]

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;

[Kurt_Bremser]

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.

[mghamari63]

Thank you KurtBremser!

I'll run it and let you know.
Cheers!

[mghamari63]

Thank you KurtBremser!

This is exactly what I want.
It will be appreciated if you let me know how to add more variables from dataset one to program. Again the condition is if B=C then F=D;

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;
Let say I want to create a table like this:
A B C D E F
4 6 . . 7 10
3 2 . . 8 5
. . 1 3 . .
. . 2 5 . .
. . 3 8 . .
. . 4 7 . .
. . 6 10 . .

Thank you for your time.

[Kurt_Bremser]

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;

[mghamari63]

Thank you so much sir!
I appreciated your explanations.
It works correctly.