topic Re: Creating a new variable using two dataset with different var names and observations in SAS Data Management

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

mghamari63 — Wed, 01 Apr 2020 13:26:38 GMT

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!

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

PeterClemmensen — Wed, 01 Apr 2020 13:29:41 GMT

What if multiple values in C equals A?

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

mghamari63 — Wed, 01 Apr 2020 13:33:25 GMT

Thanks!

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

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

PeterClemmensen — Wed, 01 Apr 2020 13:35:16 GMT

What is the logic then?

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

mghamari63 — Wed, 01 Apr 2020 13:40:47 GMT

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;

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

Ksharp — Wed, 01 Apr 2020 13:41:07 GMT

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;

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

Kurt_Bremser — Wed, 01 Apr 2020 13:49:58 GMT

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.

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

mghamari63 — Wed, 01 Apr 2020 14:22:09 GMT

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!

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

mghamari63 — Wed, 01 Apr 2020 15:33:18 GMT

Thank you KurtBremser!

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

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

mghamari63 — Wed, 01 Apr 2020 19:30:39 GMT

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.

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

Kurt_Bremser — Thu, 02 Apr 2020 09:44:29 GMT

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;

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

Ksharp — Thu, 02 Apr 2020 11:08:55 GMT

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;

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

mghamari63 — Thu, 02 Apr 2020 14:38:09 GMT

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