topic Re: Limitation of sql procedure macro in SAS Procedures

Limitation of sql procedure macro

Mike_Davis — Tue, 08 May 2012 15:58:44 GMT

Hello everyone.

If data set One is a very big dataset(too many rows) there would be an error when running code as below(two methods of using macro):

proc sql noprint;

select site into :site1 - :site1000000

from One;

select value into :value1 - :value1000000

from One;

quit;

Could anyone tell me any information of this limitation? and any solution?

Thanks!

Message was edited by: Mike Davis

Re: Limitation of sql procedure macro

shivas — Tue, 08 May 2012 16:05:27 GMT

Hi Mike,

Minor modification to your code

proc sql noprint;

select LastName into :Lastname1 - :Lastname1000000

from One;

Thanks,

Shiva

Re: Limitation of sql procedure macro

Reeza — Tue, 08 May 2012 16:06:43 GMT

Do you run into an error for part 1 or the second one?

My guess is you're running into a limit on the size of a macro variable which is either 32K or 64K characters depending on your bit and SAS version for the second part.

You need to specify a bit more about what you're doing when you run into the error and why you need the macro variables that way before people can present workarounds.

SAS offers one here:

http://support.sas.com/kb/46/109.html

Re: Limitation of sql procedure macro

Linlin — Tue, 08 May 2012 16:06:51 GMT

It has maximum length of 65534 characters.

Re: Limitation of sql procedure macro

Mike_Davis — Tue, 08 May 2012 16:09:24 GMT

maximum length of 65535 is for first or second method?

Thanks

Re: Limitation of sql procedure macro

Linlin — Tue, 08 May 2012 16:15:39 GMT

I believe it is for your second:

select FirstName into :firstname separated by ' '

from One;

quit;

Re: Limitation of sql procedure macro

FloydNevseta — Tue, 08 May 2012 16:21:28 GMT

I think the bigger question is why do you want to create that many macro variables? What do you intend to do with them? Your answers will provide clues to possible alternatives.

Re: Limitation of sql procedure macro

Mike_Davis — Tue, 08 May 2012 18:04:23 GMT

I want use the batch of macro variables to update a dataset using proc sql update.

Thanks

Re: Limitation of sql procedure macro

Reeza — Tue, 08 May 2012 18:05:04 GMT

Not enough info

Re: Limitation of sql procedure macro

FloydNevseta — Tue, 08 May 2012 18:53:48 GMT

Be more specific. I'm quite sure that there might be a way to update the dataset without creating such large macro variables. You've already mentioned that you have a dataset called One that has first and lastnames. What does the other dataset(s) look like and how does first and lastname relate the dataset(s)?

Re: Limitation of sql procedure macro

Ksharp — Wed, 09 May 2012 04:33:19 GMT

Maybe you can check some system options about macro variable like:

MEXECSIZE

MSYMTABMAX=

Ksharp

Re: Limitation of sql procedure macro

Mike_Davis — Wed, 09 May 2012 13:06:16 GMT

Hi Ksharp,

Do you know what is the max value to set MEXECSIZE and MSYMTABMAX options?

Thanks!

Re: Limitation of sql procedure macro

Mike_Davis — Wed, 09 May 2012 13:11:58 GMT

Hi SAS_Bigot,

The other dataset's name is two,it need to be updated ,the dataset have site1 site2 site 3 ,It need to be updated by dataset one. I modified dataset one,

the column's name in dataset one is not "firstname" and "lastname",they are "site" and "value"(I updated my question),the site column have three different possible values they are 1,2 or 3.(for example :2 correspond to site2 in dataset two)

for instance if ID=3 then site=2,value=789621,we need to update two's third row(ID=3),site2 should be replaced from 33 to 789621.so and so forth.. the two dataset have public column "ID"

Thanks!

dataset one:

ID site value

1 1 78645

2 1 5686

3 2 789621

4 3 78742

5 3 45689

6 2 1479789

7 1 4678

8 2 36708

.....

dataset two:

ID site1 site2 site 3

1 8 . .

2 . 12 66

3 88 33 9

4 5 22 99

5 .

6 6 1 7

.....

Re: Limitation of sql procedure macro

ballardw — Wed, 09 May 2012 15:24:13 GMT

Since MSYMTABMAX is the memory allocated for the macro table (i.e. space for all macro stuf) minimum is the number of macro variables you are using times the size of each one PLUS all of the SYSTEM macro variables. So you have 1,000,000 sites and values

minimum is looking like 1000000*(length of SITE variable) + 1000000* (formatted length of VALUE variable).

My default for the MSYMTABMAX is 4194304 so that looks way to small for what you are attempting.

Note that the syntax allows you to allocate bytes, kilobytes, megabytes or gigabytes.

Re: Limitation of sql procedure macro

FloydNevseta — Wed, 09 May 2012 16:13:28 GMT

Here's an option that doesn't require macro variables, but it's not a sql update.

* I added a couple of values for multiple ;
* sites within the same id;
data one;
infile cards;
input ID site value;
cards;
1 1   78645
1 2   11111
2 1   5686
3 2   789621
4 1   22222
4 3   78742
5 3   45689
6 2   1479789
7 1   4678
8 2   36708
;
run;

data two;
infile cards missover;
input ID   site1 site2 site3;
cards;
1 8   .   .
2 .   12 66
3 88 33 9
4 5   22 99
5
6 6   1   7
7
8
;
run;

proc transpose data=one out=onet (drop=_:) prefix=tsite;
by id;
var value;
id site;
run;

data two_updated (drop=_: tsite:);
merge two onet;
by id;
array site(*) site1-site3;
array tsite(*) tsite1-tsite3;
do _i = 1 to 3;
site(_i) = coalesce( tsite(_i), site(_i) );
end;
run;

Re: Limitation of sql procedure macro

Ksharp — Thu, 10 May 2012 06:38:24 GMT

Sorry. I don't know. Like ballardw said. Did you check it at susport.sas.com

or at SAS documentation.