BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Mike_Davis
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
FloydNevseta
Pyrite | Level 9

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;

View solution in original post

15 REPLIES 15
shivas
Pyrite | Level 9

Hi Mike,

Minor modification to your code

proc sql noprint;

select LastName into :Lastname1 - :Lastname1000000

from One;

Thanks,

Shiva

Reeza
Super User

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
Lapis Lazuli | Level 10

It has maximum length of 65534 characters.

Mike_Davis
Fluorite | Level 6

maximum length of 65535 is for first or second method?

Thanks

Linlin
Lapis Lazuli | Level 10

I believe it is for your second:

select FirstName into :firstname separated by '  '

from One;

quit;

FloydNevseta
Pyrite | Level 9

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
Fluorite | Level 6

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

Thanks

Reeza
Super User

Not enough info

FloydNevseta
Pyrite | Level 9

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
Fluorite | Level 6


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
Pyrite | Level 9

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
Super User

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

MEXECSIZE

MSYMTABMAX=

Ksharp

Mike_Davis
Fluorite | Level 6

Hi Ksharp,

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

Thanks!


ballardw
Super User

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.


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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