03-23-2015 06:20 PM
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.
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;
03-23-2015 07:01 PM
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.
03-23-2015 08:45 PM
I just want to know the best way to splitt 42000 unique records into 6-7 small datasets.
set test1 (firstobs=1 obs=6000);
set test1(firstobs=6001 obs=12002);
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?
03-23-2015 07:09 PM
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.