topic Re: How to merge a large number of datasets in a library without producing errors in SAS Programming

How to merge a large number of datasets in a library without producing errors

sunman — Fri, 25 Feb 2022 14:15:47 GMT

Hello,

I want to merge more than 1000 datasets in a SAS Library into one (All their names start with V if that helps). SAS generates this error when I run my program "ERROR: CLI error trying to establish connection: The permitted number of client connections(510) has been exceeded".

Below is the code I use:

proc sql noprint;
/* select memname into : names separated by ' ' */
select catx('.',libname,memname)
into :names separated by ' '

from dictionary.tables
where libname='PD';

data want;
set &names;
run;

I also used macro and SQL UNION ALL as an alternative, but then there was this error:

"ERROR: The text expression length (65538) exceeds maximum length (65534). The text expression has been truncated to 65534 characters."

I guess the problem is that I have way too many datasets. Can you please help me here? About my level of expertise, I'm just a rookie.

Many thanks in advance,

Re: How to merge a large number of datasets in a library without producing errors

andreas_lds — Fri, 25 Feb 2022 14:40:21 GMT

Initially, we need to clarify if you want to merge the datasets or append them. The data step you have posted, appends the dataset.

Re: How to merge a large number of datasets in a library without producing errors

Quentin — Fri, 25 Feb 2022 14:55:13 GMT

You can use data set lists on a SET statement, so perhaps:

data want;
set pd.v: ;
run;

That will avoid the problem with the macro variable lengths. You may still get the database multiple connections limit error, I haven't seen that before. There may be an option to increase that limit (either on SAS side, or database side).

Re: How to merge a large number of datasets in a library without producing errors

PaigeMiller — Fri, 25 Feb 2022 15:17:03 GMT

CALL EXECUTE could be a way to get around the restriction on the length of macro variables. However, this would not address the error "The permitted number of client connections(510) has been exceeded". Are these data sets that you are trying to merge in more than 510 different libraries??

Re: How to merge a large number of datasets in a library without producing errors

Tom — Fri, 25 Feb 2022 15:16:35 GMT

So PD is pointing to some external database?

Do the datasets all have the exact some structure? If so then generate 1000 PROC APPEND calls instead. That will only need one database connection per PROC step.

proc append data=pd.table1 base=want force;
run;
proc append data=pd.table2 base=want force;
run;
...

You might also try using pass thru SQL to the remote database. But they probably also will hit limits if we really need to read 1000 tables at once.

Finally you could do it in chunks. Say the limit is 20 table connections. So make 50 temporary datasets from 20 tables each. Then combine the 50 temporary datasets into the target dataset.