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

[ChickenLittle]

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.

[ballardw]

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.

[ChickenLittle]

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

[SASKiwi]

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. 

[Reeza · Accepted solution]

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.

---

[data_null__]

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;

[ChickenLittle]

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