topic Re: Pull in a new field from different tables based on table name in a field from the original table in SAS Programming

Pull in a new field from different tables based on table name in a field from the original table.

ChickenLittle — Mon, 26 Feb 2024 22:03:37 GMT

I have a table that contains a field named TableName, which stores the name of the specific table I need to pull from for each observation (re: each observation requires me to hit against different tables to grab NeededField). For each observation, is there a way to code it so that depending on the table name in the TableName field, I can pull the field I need just from that table for that particular observation?

Example:

libname abc 'path/of/my/file';

OriginalTable

obs key TableName

1 cat abc.202201

2 dog abc.202202

3 frog abc.202303

Table at abc.202201

key NeededField

cat 1234

dog 12345

frog 123456

Table at abc.202202

key NeededField

cat 345

dog 3456

frog 34567

Table at abc.202202

key NeededField

cat 5

dog 56

frog 567

I want to see:

OriginalTable

obs key TableName NeededField

1 cat abc.202201 1234

2 dog abc.202202 3456

3 frog abc.202303 567

Is this at all possible? Thank you in advance.

Re: Pull in a new field from different tables based on table name in a field from the original table

ballardw — Mon, 26 Feb 2024 22:14:06 GMT

The table names you show are not normally valid SAS data set names. So are those "tables" SAS data sets or something in an external data system?

Basically you are going to use the information you have write additional syntax. Possible? yes. Short and sweet? Maybe not.

Is the Key that you show going to be unique in each of the tables? If the key is not unique there will need to be additional rules provided on how to select the specific observation from each external data set.

Re: Pull in a new field from different tables based on table name in a field from the original table

ChickenLittle — Mon, 26 Feb 2024 22:16:59 GMT

All data tables are sas data sets and the key is unique.

Re: Pull in a new field from different tables based on table name in a field from the original table

SASKiwi — Mon, 26 Feb 2024 22:34:11 GMT

How many tables, columns and rows are in your real use case? This is a really inefficient methodology for locating and reading your required data and is unlikely to scale well to large numbers of tables, columns and rows. If you explained your problem statement, there may be better approaches.

Re: Pull in a new field from different tables based on table name in a field from the original table

Reeza — Mon, 26 Feb 2024 22:36:36 GMT

Doable, yes, efficient, probably not.

One option: combine all into one table with the source table identified using INDSNAME option. Then merge for desired results on the two key variables. You could limit to just key values needed if size might be an issue.

data combined;
set abc.202201-abc.202203 indsname =src;
tableName=src;
keep key NeededField tableName;
run;

proc sql;
create table want as
select a.*, b.needed_field
from have as a
left join combined as b
on upcase(a.TableName)=upcase(b.TableName)
order by 1;
quit;

@ChickenLittle wrote:

I have a table that contains a field named TableName, which stores the name of the specific table I need to pull from for each observation (re: each observation requires me to hit against different tables to grab NeededField). For each observation, is there a way to code it so that depending on the table name in the TableName field, I can pull the field I need just from that table for that particular observation?

Example:

libname abc 'path/of/my/file';

OriginalTable

obs key TableName

1 cat abc.202201

2 dog abc.202202

3 frog abc.202303

Table at abc.202201

key   NeededField

cat 1234

dog 12345

frog 123456

Table at abc.202202

key   NeededField

cat 345

dog 3456

frog 34567

Table at abc.202202

key   NeededField

cat 5

dog 56

frog 567

I want to see:

OriginalTable

obs key TableName NeededField

1 cat abc.202201 1234

2 dog abc.202202 3456

3 frog abc.202303 567

Is this at all possible? Thank you in advance.

Re: Pull in a new field from different tables based on table name in a field from the original table

data_null__ — Mon, 26 Feb 2024 22:36:43 GMT

You could just make a single lookup table and do a KEYed set.

data a;
   length tableName $16;
   tableName = 'abc.202201';
   input key $ field;
   cards;
cat         1234
dog        12345
frog        123456
;;;;
data b;
   length tableName $16;
   TableName = 'abc.202202';
   input key $ field;
   cards;
cat         345
dog        3456
frog        34567
;;;;
data c;
   length tableName $16;
   TableName = 'abc.202303';
   input key $ Field;
   cards;
cat         5
dog        56
frog       567
;;;;
   run;
data lookuptable(index=(idx1=(tableName key)/unique));
   set a b c;
   run;
data want;
   set have;
   set lookuptable key=idx1/unique;
   run;
proc print;
   run;

Re: Pull in a new field from different tables based on table name in a field from the original table

ChickenLittle — Mon, 26 Feb 2024 23:34:07 GMT

Thank you so much for your response! This works too, but I used the other responder's suggestion first. This was very helpful!