topic Combining multiple big tables - set or hash table? in SAS Programming

Combining multiple big tables - set or hash table?

MargoBlue — Mon, 10 Feb 2020 15:39:34 GMT

I would like to set 12 tables (this scope can change, f.e. it can be 36 tables from: 2017, 2018, and 2019 year), where one table has about 20 milions rows.

I thought about creating view (at this moment only for year 2019):

data tab01_1/ view=tab01_1;

set tab01_2019:;

run;

But then I cannot make any operations on the view. f.e. when I try make proc sort, I have an error:

proc sort data = tab01_1;

by id;

run;

ERROR: Cannot sort in place.

ERROR: Unable to create WORK.tab01_1.DATA because WORK.tab01_1.VIEW already exists

What is the best way to set tables? Can I uIse UNION operator instead of SET?

Or maybe hash tables will be a good option? How it should look like for so many tables and records?

After creating so big table can I make some operations on its then?

Re: Combining multiple big tables - set or hash table?

Tom — Mon, 10 Feb 2020 15:47:57 GMT

You cannot overwrite a view with a dataset with the same name. Just tell PROC SORT what dataset you want to write the sorted data into.

proc sort data = tab01_1 out=tab01_1_sorted;
  by id;
run;

Note that if the original datasets that the view is reading are already sorted you can preserve the order by adding the BY to the view definition and eliminate the need to make a copy into a sorted dataset.

data tab01_1/ view=tab01_1;
  set tab01_2019:;
  by id;
run;

Re: Combining multiple big tables - set or hash table?

Tom — Mon, 10 Feb 2020 16:01:28 GMT

In general it is much easier to keep the data for all years in one dataset and then use WHERE statement when using it to limit to the range you want. But whether that will work for you depends on how it is created and how it is used.