topic Re: Optimization - concatenate multiple views in SAS Programming

Optimization - concatenate multiple views

MargoBlue — Thu, 20 Feb 2020 15:38:16 GMT

I have a macro: %macro tab(start_date, periods), which creates multiple views dependent on the start date and number of periods.

For example: %tab(201701, 36) creates views: tab_201701, tab_201702,tab_201703,...tab_201912, tab_202001.

I want to concatenante this views and create table:

data all_tab;
set WORK.tab_:;
by id1 id2;
run;

/*or*/

data all_tab;
set WORK.tab_: open=defer;
run;

But these views have about from 700 thousands to 1,2 milion records and (only :)) 3 columns.

It takes a long time, and start date as well as number of periods can change (even start_date =201201), so the numer of views can be different. I know that I can create another view as: data all_tab/view=all_tab; ,

but I need the table - to make some operations on its later.

Could you tell me what is better way to make this data step more efficient?:

data all_tab;

set WORK.tab_:;

by id1 id2;

run;

I've read about hash tables but I don't know how use it to concatenate views WORK.tab_: and if it works on views.

Thank you in advance.

Re: Optimization - concatenate multiple views

Reeza — Thu, 20 Feb 2020 16:30:41 GMT

PROC APPEND will be slightly faster but if you're appending 36 files that are each about 1 million records your main file will be 36 million records, which is a big file, think a few gigabytes.

It all really depends on what you're doing next.

Re: Optimization - concatenate multiple views

Reeza — Thu, 20 Feb 2020 16:30:43 GMT