How to assign a value from a table to another table according to table's name

[yanshuai]

Hello all,

Hope you all well.

I have two libraries: 'SuperHero' and 'Height'

In library 'SuperHero', it has 1000 datasets with different names, for example, the 1000 datasets name are

Bumblebee,

Starscream,

Batman,

Wonderwoman,

...etc

In library 'Height', it has only one dataset, having two variables, one is each dataset's name in 'SuperrHero', another variable is their height, such as

Name                 Height

Bumblebee           14.8

Starscream           22.5

Batman                 1.86

Wonderwoman      1.72

....etc

What I would like to do is, add Height variable and value to each file in 'SuperHero'

Logic is

IF Name variable in 'Height' is the same with dataset's name in 'SuperHero',

THEN add Name's corresponding Height to that dataset in 'SuperHero'.

So that, in 'SuperHero', each obs of each dataset has a new variable called Height.

For example, originally, open Bumblebee data in 'SuperHero', it is like this

 Age                   Weight

23                          345

24                          349

25                          765

After what we do, open Bumblebee data in 'SuperHero', it will be like this

Height             Age                  Weight

14.8                   23                          345

14.8                   24                          349

14.8                   25                          765

Does this need macro?

Anyway, what code should I use?

Thank you and keep warm!

[Reeza · Accepted solution]

Yes it needs a macro. 

I would recommend you first do this for your 'base case' and get working code and then figure out how to make it dynamic and automatic. 

If you can assume all the data sets are listed in the HEIGHT data set then something as simple as this may be all you need. 

Untested. CALL EXECUTE has examples in the documentation if you can't get it working.

%macro add_height(libname=, dsn=, height=);

data &libname..&dsn;
set &libname..&dsn;

height = &height;
run;

%mend;

data _null_;
set height;

str = catt('%add_height(libname=', 'work', ', dsn=', dataset_name, ', height=', height, ');');

call execute(str);

run;

---

@yanshuai wrote:

Hello all,

Hope you all well.

I have two libraries: 'SuperHero' and 'Height'

In library 'SuperHero', it has 1000 datasets with different names, for example, the 1000 datasets name are

Bumblebee,

Starscream,

Batman,

Wonderwoman,

...etc

In library 'Height', it has only one dataset, having two variables, one is each dataset's name in 'SuperrHero', another variable is their height, such as

Name                 Height

Bumblebee           14.8

Starscream           22.5

Batman                 1.86

Wonderwoman      1.72

....etc

What I would like to do is, add Height variable and value to each file in 'SuperHero'

Logic is

IF Name variable in 'Height' is the same with dataset's name in 'SuperHero',

THEN add Name's corresponding Height to that dataset in 'SuperHero'.

So that, in 'SuperHero', each obs of each dataset has a new variable called Height.

For example, originally, open Bumblebee data in 'SuperHero', it is like this

 Age                   Weight

23                          345

24                          349

25                          765

After what we do, open Bumblebee data in 'SuperHero', it will be like this

Height             Age                  Weight

14.8                   23                          345

14.8                   24                          349

14.8                   25                          765

Does this need macro?

Anyway, what code should I use?

Thank you and keep warm!

---

[yanshuai]

Sorry back to you late. But this works perfect.

Thank you Reeza.

[Astounding]

Not sure if this might be simpler to follow.  Using the HEIGHT data set, and assuming that a LIBNAME statement defines SuperHero as the folder holding all the SAS data sets:

data _null_;

set height;

call execute ('data superhero.' || name || '; set superhero.' || name || '; height=' || put(height, best8.) || '; run;') ;

run;

Since you are replacing permanent SAS data sets, be sure to have backups available during the testing phase in case something goes wrong!

[novinosrin]

@yanshuai You wrote- "Does this need macro? Anyway, what code should I use?"

If memory isn't a constraint, the same can be achieved using Hash look up. Nevertheless, not as easy as call execute to stack the sas code one by one. Of course, if you are big time hash object user so to speak, it is not hard.