Help using Base SAS procedures

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

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

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

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!

 


Accepted Solutions
Solution
‎03-01-2018 04:58 PM
Super User
Posts: 23,776

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

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!

 


 

View solution in original post


All Replies
Solution
‎03-01-2018 04:58 PM
Super User
Posts: 23,776

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

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!

 


 

Contributor
Posts: 50

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

Sorry back to you late. But this works perfect.

Thank you Reeza.

Super User
Posts: 6,785

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

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!

PROC Star
Posts: 1,836

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

@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. 

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 334 views
  • 0 likes
  • 4 in conversation