Combining two tables based on observations in a separate table (in the EG)?
In the Enterprise Guide, I have a table (called COUNTRIES
) containing the name of some countries in the world in one column and the currency of that country in the second column.
eg.
CTRY | CRNCY
------------------------
UK | GBP
US | USD
FR | EUR
AU | AUD
etc.
This table is only a small subset of all countries in the world and ranges from 10 to 20 observations depending on preference. The number of entries in this table can change at any time.
For each country listed in COUNTRIES
, I have a table containing information about that country (for example, for the example above, I have tables called CTRY_UK, CTRY_US, CTRY_FR, CTRY_AU
, etc.), as well as for their currencies (so I also have CRNCY_GBP, CRNCY_EUR
etc. etc.)
Now for each observation in COUNTRIES
eg ( UK
and GBP
), I want to join the table CTRY_UK
with the table CRNCY_GBP
, but I don't know how to do it so in SAS
.
In other words, I want to join two tables based on the records specified in a separate table. How can I do that?
source to share
You can read data values into macro variables using the call open
and functions call set
, and then write any code you need using 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
source to share
The best approach here is likely to generate code from an initial table and then run it as a macro message.
So, imagine the call is something like
%macro join_my_Tables(country=,currency=);
create table &country. as
select whatever stuff from ctry_&country., crncy.¤cy.
...
;
quit;
%mend join_my_Tables;
Then you will create calls from this:
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;
This I suspect will do what you want. You may need to modify it a bit if your different tables have different aspects to them (why they are separated is anyway a silly way of storing data, unless the columns are different from each other and you really don't need a vertical structure for some cause).
If you have very different sets of columns and don't want to rely on select *
, you may need to create a dataset that stores this information and pulls it out during macro execution.
source to share