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?

score 1 · Accepted Answer · answered Aug 18 '14 at 17:23

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

score 0 · Answer 2 · answered Aug 18 '14 at 17:15

The best approach here is likely to create the code from the initial table, and then run that as a macro call.

So imagine the call is something like

%macro join_my_Tables(country=,currency=);
 create table &country. as
   select whatever stuff from ctry_&country., crncy.&currency.
    ... 
    ;
 quit;
%mend join_my_Tables;

Then you would create calls ot that:

proc sql;
  select cats('%join_my_Tables(country=',ctry,',currency=',crncy,')')
    into :calllist separated by ' '
    from tbl1;
quit;

*not technically needing to be a separate proc sql here, just to show it is doing something else;
proc sql;
  &calllist. 
quit;

That would do what you want, I suspect. You might need to modify it some if your various tables have different aspects to them (why are they separate, anyway; that's a silly way to store data, unless the columns are very different and you really don't want a vertical structure for some reason).

If you have very different sets of columns, and don't want to rely on select *, then you may need to create a dataset that stores this information and pull it out during the macro execution.

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

2 Answers2