bufsize and proc sql

emsmpa — Mon, 07 Oct 2013 17:15:27 GMT

Hi,

we are trying to increase the bufsize for newly created tables.

Please see the following example. The system value is set to it's maximum of almost 2G:

proc sql;

create table test1 (bufsize = max)

(

DATE num format=YYMMDD10. informat=YYMMDD10.,

TEST_KEY char(20) format=$20.,

AMOUNT num format=8.2 length=6

);

describe table test1;

quit;

This creates the following output:

NOTE: SQL table WORK.TEST1 was created like:

create table WORK.TEST1( bufsize=2630656 )

(

DATE num format=YYMMDD10. informat=YYMMDD10.,

TEST_KEY char(20) format=$20.,

AMOUNT num format=8.2

);

Where does this value of ~ 2MB come from? When we increase the amount of test data, this value is going up to ~8MB, but not more.

This is causing us I/O problems.

My questions are:

1. How does SAS create the bufsize value of ~2MB?

2. How can i force it to use a bigger value of e.g. 1GB?

Thanks in advance!

Re: bufsize and proc sql

snoopy369 — Mon, 07 Oct 2013 17:39:07 GMT

I get the same effective maximum (~2.5MB). I do get a warning, saying that my maximum (2g) is higher than the allowable limit (around 1g). Do you get the same? That's with a memsize of 6g, which doesn't surprise me - from other evidence I can see that SQL uses around 5x as much space (1g * 5) as it needs.

topic Re: bufsize and proc sql in SAS Programming

bufsize and proc sql

Re: bufsize and proc sql