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?
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.