Limitation of sql procedure macro

[Mike_Davis]

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

[shivas]

Hi Mike,

Minor modification to your code

proc sql noprint;

select LastName into :Lastname1 - :Lastname1000000

from One;

Thanks,

Shiva

[Reeza]

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

[Linlin]

It has maximum length of 65534 characters.

[Mike_Davis]

maximum length of 65535 is for first or second method?

Thanks

[Linlin]

I believe it is for your second:

select FirstName into :firstname separated by '  '

from One;

quit;

[FloydNevseta]

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.

[Mike_Davis]

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

Thanks

[Reeza]

Not enough info

[FloydNevseta]

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)?

[Mike_Davis]

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

7

8

9

10

.....

[FloydNevseta · Accepted solution]

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;

[Ksharp]

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

MEXECSIZE

MSYMTABMAX=

Ksharp

[Mike_Davis]

Hi Ksharp,

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

Thanks!

[ballardw]

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.