topic Re: Joining multiple data sets in a library with conditions in SAS Procedures

Joining multiple data sets in a library with conditions

mjheever — Fri, 06 Feb 2015 12:36:29 GMT

Greetings everyone !

I have this issue where I want to join multiple data sets in a specific library together.

Say you have library LibExample which consists of a number of tables by date.

LibExample:

table_A_201310

table_B_201310

table_A_201311

table_B_201311

table_A_201312

table_B_201312

table_A_201401

table_B_201401

table_A_201402

....

table_A_201412

All the tables are in the same format and have the same attributes.

Say I have a different table consisting of a selection of specific ID's, call this table_ID.

I want to get all the observations from the date tables which matches the ID's in the table_ID.

Here is an example of my current code:

%macro get() ;

Proc sql noprint;

Select ID_No into :accnts separated by ", " from table_ID;

QUIT;

proc contents data = LibExample._all_ out = All_tables noprint;

run;

proc sort data = All_tables nodupkey;

by descending memname ;

run;

data All_Tables_Names(drop=i);

set All_tables(keep=MEMNAME);

where memname like "Table_A_%";

i = monotonic();

if i <= 13 then output; /*takes the latest year's table*/

run;

data _null_;

set All_Tables_Names END = EOF;

by descending memname ;

file "&work./Tables.sas";

format putstr $1000.;

if _n_ = 1 then do;

put 'data Combined_Tables; set ';

end;

if first.memname then do;

putstr = "Libexample." || compress(memname) ||;

put putstr ;

end;

if EOF then do;

put ";";

putstr = "where ID_No in (&accnts.);";

put putstr ;

put "run;";

end;

run;

%include "&work./Tables.sas";

%mend;

%get();

The underlined part is where presume the problem is, since I'm getting an the follow error message: ERROR: Syntax error while parsing WHERE clause.

I'm not sure if there is a 'better' or more efficient way to pair the 'where' clause with, regarding an array of values.

I've tried to play around with put and input functions to resolve the issue but had no luck.

Please note that ID_No is a numeric variable.

Thank you

Re: Joining multiple data sets in a library with conditions

Kurt_Bremser — Fri, 06 Feb 2015 12:59:25 GMT

proc sql;

create tables all_tables as select trim (libname)!!'.'!!trim(memname) as memname from dictionary.tables where libname = "LIBEX";

quit;

proc sort data=all_tables;

by descending memname;

run;

data all_tables;

set all_tables (obs=12);

run;

proc sql;

select memname into :tables separated by " " from all_tables;

quit;

data want;

set &tables;

if _n_ = 1 then do;

length ID_No 8;

declare hash h(dataset:"table_id");

h.definekey("ID_No");

h.definedone();

call missing (ID_No);

end;

rc = h.find();

if rc = 0 then output;

drop rc;

run;

Did it without testing, so you may have to fiddle with the hash object definition and usage.

Update: did some corrections, should work now.

Re: Joining multiple data sets in a library with conditions

RW9 — Fri, 06 Feb 2015 13:16:34 GMT

Assuming I understand your logic:

data table_a_201310 table_b_201310 table_a_201311 table_b_201311;
id="001"; a="a"; output;
id="002"; a="b"; output;
id="003"; a="c"; output;
run;

data ids;
id="001"; output;
id="003"; output;
run;

proc sql;
create table WANT
(
id char(8),
a char(8)
);
quit;

data _null_;
set sashelp.vtable (where=(libname="WORK" and substr(memname,1,5)="TABLE"));
call execute('proc sql;
                  insert into WANT
                  select *
                  from    WORK.'||strip(memname)||'
                  where   ID in (select distinct ID from WORK.IDS);
                quit;');
run;

Re: Joining multiple data sets in a library with conditions

mjheever — Fri, 06 Feb 2015 13:51:06 GMT

Thank you for the reply Kurt and RW.

I will try to apply your code to this situation.

Although just to make sure I give a clear picture of my situation here is an example of the tables:

table_A_201310 (all the table_X_20yymm tables are in this form)

ID_NO	Date	Amount	Category
1231231	12OCT2013	200.63	Hardware
3453123	29OCT2013	99.95	Digital

and table_ID is a table of specific ID-numbers:

table_ID (Extracted ID numbers which I want to an analysis on)

ID_NO
1231231
3237132
3453123

So I have a lot of table_A_20yymm tables and also a lot of table_B_20yymm tables but I only want to match a couple of the table_A_20yymm tables to the Table_ID table and also then I want to exclude all the table_B_20yymm tables.

Essentially what I'm trying to do is to combine all the table_A_201301, table_A_201302, table_A_201303,...., table_A_201312 tables which contains all the observations of the ID_NO's in the Table_ID table.

Does this make sense ?

Re: Joining multiple data sets in a library with conditions

Kurt_Bremser — Fri, 06 Feb 2015 13:59:17 GMT

You just need a suitable where condition when reading dictionary.tables (or sashelp.vtables in a data step).

The hash method is quite robust with large lookup tables and works without sorting any of the tables.

Re: Joining multiple data sets in a library with conditions

mjheever — Fri, 06 Feb 2015 14:03:30 GMT

Yes thank you Kurt, so far that seems like the best option in this situation.

I haven't use hash method yet so I'm trying to figure it out just now - I will keep you updated on my progress