%Put macro

Occasional Contributor
Posts: 11

%Put macro


The record length of current dataset is 42,000 and I’m not sure why this error is coming up. Is there a way to avoid this error without splitting my dataset into a few small record length datasets? I splitted into two datasets (21,000 records, and 21,000 records) and still I'm getting the same error below. Your help is greatly appreciated.

197  proc sql noprint;
198       select clinic into:id_num
199       separated by ', '
200       from data_1;
ERROR: The length of the value of the macro variable id_num (65540) exceeds the maximum length (65534). The value has been
       truncated to 65534 characters.
201      quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.07 seconds
      cpu time            0.08 seconds

ERROR: The text expression length (65535) exceeds maximum length (65534). The text expression has been truncated to 65534
202  %PUT &id_num;

Grand Advisor
Posts: 10,210

Re: %Put macro

Do you really need a single variable with each clinic likely duplicated? Your result could look like:

Clinic A,  Clinic A, Clinic A, Clinic B.

If you want a list of unique clinics

Select distinct clinic into: id_num

Or you can try adjusting your MVARSIZE system option which sets the maximum size for in memory macro variables. The default of 4096 bytes yields the 65534.

WHY? 42000 values of at least 1 character + at least 42000 commas and you get at least 84000 characters exceeding that limit.

You will need: Number of records*(length of clinic + 1) / 16 BYTES for that macro variable.

Occasional Contributor
Posts: 11

Re: %Put macro

thanks Haikuo.

I just want to know the best way to splitt 42000 unique records into 6-7 small datasets.

data data_1;
set test1 (firstobs=1 obs=6000);

data data_2;
set test1(firstobs=6001 obs=12002);

daa data_3;

set test1 (firsobs=12003 obs=24006);


and so on....

so, I'm not sure if this is the best way to break it into smalll datasets...

mvarsize option: Could you show an example how could i use it?

Respected Advisor
Posts: 3,124

Re: %Put macro

If you google: splitting datasets site:communities.sas.com

Many good answers.

Respected Advisor
Posts: 3,124

Re: %Put macro

Whole picture will certainly help: what is your purpose? why you are putting all of the IDs into a macro variable? What is the downstream process? How you suppose to use this super long string of IDs stored in a macro variable?

Length limit of 65540 is in the unit of 'Byte'. The number of the IDs you can store in one macro variable is depending on the length of your IDs. For example, if the length of your ID is 10 bytes, then 65540/(10+1)=5958, (plus ',' is for the comma that is your choice of separator), so only 5958 IDs can be stored into one macro variable in this case, therefore it is not a surprise that you even have trouble when storing 21,000 IDs.

Tell us more of your purpose may help us to identify your core issues.


Ask a Question
Discussion stats
  • 4 replies
  • 3 in conversation