Appending Tables With Dates in the Table Names

[ChrisNZ]

Give it a go.If the hash table is too slow, the where clause should work.SAS slows down dramatically when reading many tables at once but 60 should be alright.

[paulrockliffe]

Thanks for all the help, I've got this all up and running now and it works really well!
I'm using the same approach for something else and wondered if it was simple to add a couple of extra features:

How do I modify the code so that the lookup table is joined on a key that has a different name in each table?

How do I modify it so that rather than bringing every field from one table into the resulting table it brings only columns (from both tables) that are explicitly defined?

I'm modifying another process to use the same approach, that was done in Query Builder where the Join was defined and columns chosen, so I want to mimic that rather than get the right output in a second step.

Thanks again!

[paulrockliffe]

I should add I modified the code to simply import from a particular table rather than stitch tables together as well.  I want to be able to add conditions to this import. New code is:

data Want;

if _n_ = 1 then do;

declare hash l (dataset:"Server.Lookup");

l.definekey("Ref");

l.definedone();

end;

set Server.Data;

if l.check() = 0;

run;

I'd also like to be able to limit the data coming in by adding where clauses to the above eg, where Date_Column > &Date then yes, otherwise no.

Thanks!

[Kurt_Bremser]

You can add additional conditions to the subsetting if.

[paulrockliffe]

Thanks, I think I can work that out.  I've spent all afternoon reading about hash tables and I'm struggling with getting the output table to include variables from both the has table and the table I'm filtering.  Some pointers for that would be really helpful.

Cheers

[Kurt_Bremser]

If you need the lookup hash to also deliver data, use the definedata() method to add data variables

[paulrockliffe]

Thanks, I managed to get that far, the bit I can't work out is how I output both variables from the hash table and variables from the table I'm using the hash table to subset, all in a single table. 

I'm essentially trying to do an inner join between the two tables and select columns from both.  I'm only able to get data from one table or the other.

[Kurt_Bremser]

If you have variables with identical names in both tables, you must rename them in one table to keep their values.

[paulrockliffe]

Thanks, the only value common to each table and with the same name is the one I'm using as the key.  If I only want to take that value from either table, not both, then does it still need to be renamed?  Can I not just not include the key when I use definedata?

[Kurt_Bremser]

At this point it is necessary for us to know the structures of the datasets involved, and have a picture about the types and other attributes of the variables.

The best way to do this is to post a simplified example of your "big" and "small" datasets in data steps with datalines; for these we can then design code that works (as we have something to test against), and which you can later expand to your real-life situation.