Joining two tables based on observations in a seperate table (in EG)?

[SMNBS]

Hi,

In Enterprise Guide, I have a table (called, COUNTRIES) containing the name of some countries of the world in one column, and the currency of that country in a second column.

E.g.

CTRY | CRNCY

------------------------

UK     | GBP

US     | USD

FR     | EUR

AU     | AUD

etc

This table is only a small subset of all the countries in the world, and ranges from anywhere between 10 to 20 observations depending on preference. The number of entries in this table can change at any time.

For each country specified in COUNTRIES, I have a table containing information about that country, (e.g. for the example above I have tables called CTRY_UK, CTRY_US, CTRY_FR, CTRY_AU, etc) and the same goes for their currencies (so I also have CRNCY_GBP, CRNCY_EUR, etc)

Now for each observation in COUNTRIES, for example UK,GBP, I want to join the CTRY_UK table with the CRNCY_GBP table, but I'm seeing this as a problem too difficult to solve.

In other words, I want to join two tables together based on the entries given in a seperate table. How can this be done?

[RichardinOz]

How are you going to do the merge?  You need some common variable in each table to join on to avoid a cartesian product in SQL.  If there is none a datastep merge without a by variable might work for you though the result may not be quite what you expect.

Step 1: set up a macro to do the merge - here assuming no common variable

%macro merge_ctry (ctry, crncy) ;

     data &ctry._combined ;

          merge ctry_&ctry

                    crncy_&crncy

                    ;

     run ;

%mend ;

Step 2: use a datastep to execute the macro for each ctry - crncy pair

data _Null_ ;

     set countries ;

     if exist(ctry) and exist(crncy) ;

     exec_macro = cats('%merge_ctry(', ctry, ', ', crncy, ');') ; /* single quotes essential */

     call execute (exec_macro) ;

run ;

NB the macro invocations will be streamed to execute after the data step terminates.

Obviously this is untested but it is a viable approach.

Richard