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

Question

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 and GBP), I want to join the CTRY_UK table with the CRNCY_GBP table, but I don't know a way of doing so in SAS.

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

Answer 1

You can read the data values into macro variables using the call open and call set functions, and then write whatever code you need using the macro variables.

%macro Combine;
    ** open Countries data in input mode;
    %let dsid = %sysfunc(open(Countries, i));
    ** set up reading of values into macro variables of the same name;
    %syscall set(dsid);
    ** read first observation;
    %let rc = %sysfunc(fetch(&dsid));

    %do %while (&rc = 0);
        ** merge data sets using the auto-filled &Cntry and &Crncy macro variables;
        data merged_&Cntry;
            merge CNTRY_&Cntry CRNCY_&Crncy;
            by ID;
        run;
        ** read next observation;
        %let rc = %sysfunc(fetch(&dsid));
    %end;
    ** close data set;
    %let rc = %sysfunc(close(&dsid));
%mend;

**  actual macro call;
%Combine