Help using Base SAS procedures

Limitation of sql procedure macro

Accepted Solution Solved
Reply
Regular Contributor
Posts: 222
Accepted Solution

Limitation of sql procedure macro

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


Accepted Solutions
Solution
‎05-09-2012 12:13 PM
Frequent Contributor
Posts: 101

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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=_Smiley Happy prefix=tsite;
by id;
var value;
id site;
run;

data two_updated (drop=_: tsiteSmiley Happy;
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;

View solution in original post


All Replies
Super Contributor
Posts: 349

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

Hi Mike,

Minor modification to your code

proc sql noprint;

select LastName into :Lastname1 - :Lastname1000000

from One;

Thanks,

Shiva

Super User
Posts: 19,814

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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

Super Contributor
Posts: 1,636

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

It has maximum length of 65534 characters.

Regular Contributor
Posts: 222

Re: Limitation of sql procedure macro

maximum length of 65535 is for first or second method?

Thanks

Super Contributor
Posts: 1,636

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

I believe it is for your second:

select FirstName into :firstname separated by '  '

from One;

quit;

Frequent Contributor
Posts: 101

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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.

Regular Contributor
Posts: 222

Re: Limitation of sql procedure macro

Posted in reply to SAS_Bigot

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

Thanks

Super User
Posts: 19,814

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

Not enough info

Frequent Contributor
Posts: 101

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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

Regular Contributor
Posts: 222

Re: Limitation of sql procedure macro

Posted in reply to SAS_Bigot


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

.....

Solution
‎05-09-2012 12:13 PM
Frequent Contributor
Posts: 101

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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=_Smiley Happy prefix=tsite;
by id;
var value;
id site;
run;

data two_updated (drop=_: tsiteSmiley Happy;
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;

Super User
Posts: 10,028

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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

MEXECSIZE

MSYMTABMAX=

Ksharp

Regular Contributor
Posts: 222

Re: Limitation of sql procedure macro

Hi Ksharp,

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

Thanks!


Super User
Posts: 11,343

Re: Limitation of sql procedure macro

Posted in reply to Mike_Davis

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.


🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 15 replies
  • 337 views
  • 4 likes
  • 7 in conversation