SAS Support Communities

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? ... View more

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. ... View more

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 ... View more

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! ... View more

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! ... View more

Yeah, this table would have been setup decades ago, I suspect it's like this because something was simpler to integrate with another system like this. It goes back to a time when very little consideration was given to people actually wanting to access the data. Not something I have any control over and it's a legacy system, so it's not going to get changed to help me! I've started integrating the new system's data with this and it's at least been done properly! ... View more

Thanks, there's 17,000 values in my 'small' table and millions in the big table, so I think the hash table approach is the one. ... View more

Thanks, I'll have a look and see how it goes..... ... View more

I did put it in the first post. In the UK the Tax year is a fairly common concept, with month one being April and month 12 being March, though this data should have been set up using Calendar months as it's a total pain to work with, for no benefit at all. ... View more

I changed &start_date. to "&start_date"d and the same for &end_date and that seems to have worked.   However it looks like the date offset I described isn't being covered - 202001 is April, 202002 is May etc: I have tables up to April 2020, which is 202001, but if I put the end date as 01 April 2020, I get errors:   name_20202 does not exist, name_202003 does not exist.  From the April 2020 date, nothing past 202001 should be requested.   I've offset my dates by a couple of months to get the right period while that problem gets sorted and it looks like I'm getting the right data, certainly the volume looks correct anyway.   So thanks so much for getting me this far, it's a huge help as I was using a legacy system to run some old SQL to create a .csv file and then importing it into SAS, this will let me automate the process and strip a load of time out. ... View more

Thanks Kurt, I'm just trying to get your solution to work.  I've not used date prompts in a while, it's throwing an error because when I set the Prompt up as a Day, it passes the value "31Jan2015" to the code.  I'm presuming that's not in the right format though.  How does that date need to be setup? ... View more