The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

Reply
New Contributor
Posts: 3

The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

Hi All,

I would appreciate you if you could give the solution for the below error.

proc sql ;
select distinct(compress(quote(CONTRACT))) into: contract1 separated by ',' from holdi_1;

quit;

ERROR: The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters.

proc sql ;

create table claims as

select * from dsnw.CLAIMS

where contract_nbr in (&contract1);

quit;

Note : My objective is to take the distinct contract numbers  into the macro variable CONTRACT1 from the data set holdi_1 and will be used this macro variable in the second proc sql.Here dsnw.claims is the tablle in the database.The contract numbers from the database table which are matched with distinct contract numbers from the macro variable  CONTRACT1 will be stored into the claims data set.

Please help me for solving this error.

Regards,

Chandu

New Contributor
Posts: 3

The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

These queries ran in SAS Enterprise Guide.

Thanks,

Chandu

Super User
Super User
Posts: 6,317

The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

There is a limit to the number of characters you can stuff into a macro variable. You need to change your method.

Why do you want to store the contract numbers in a macro variable anyway when you already have them in a dataset?

proc sql ;

  create table claims as

    select * from dsnw.CLAIMS

    where contract_nbr in

          (select distinct contract from holdi_1)

  ;

quit;

New Contributor
Posts: 3

Re: The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

Thank you very much for your suggestions on this issue.

The contract numbers are extracted from the db2 tables(ROTSG,COVSG ) and stored into the data set holdi_1  .From this data set ,I am taking distinct contract numbers into the macro variable contract1.Have a look into the below proc sql query.

proc sql ;
select distinct(compress(quote(CONTRACT))) into: contract1 separated by ',' from holdi_1;

quit;

In the below query ,the contract numbers(contract_nbr) in the table CLAIMS table (db2 table) which are matched to the distinct contract numbers in the data set holdi_1( macro variable contract1)  and will be stored into the data set claims.

proc sql ;

create table claims as

select * from dsnw.CLAIMS

where contract_nbr in (&contract1);

quit;

I have used below query but it is taking more time for execution.It takes approximately 40 min to run  this query .The table CLAIMS  having 2 years data , distinct contract numbers in the data set  holdi_1 only 17000 . I am researching on this issue. Is there any alternative methods for solving this issue?

proc sql ;

create table claims as

select * from dsnw.CLAIMS

where contract_nbr in

(select distinct contract from holdi_1)

;

quit;

Regards,

Chandu

Super User
Super User
Posts: 6,317

Re: The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

You need to get the query to execute in the DB2 database.  You might try moving the query that generates HOLDI_1 temporary SAS dataset into the query and see if SAS can optimize it to run fully in the DB2 database.

Otherwise switch from using the library engine to using passthrough code in SQL.

Something like:

proc sql ;

  connect to db2 .... ;

  create table claims as select * from connection to db2

   (select * from dsnw.claims where contract_nbr in

      (select contract from dsnw.rotsg union select contract from dsnw.covsg)

   )

;

quit;

Valued Guide
Posts: 2,174

The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (65534). The value has been truncated to 65534 characters

If (as I have found) you need to take contract numbers from one data base and apply to another, you won't optimise in either data base, nor in a join within the SAS environment.

As you cannot put all of these contract numbers into one macro variable, just try using two (or however many you need):

where contract_nbr in (&contract1, &contract2 );

To generalise this is easier (imho) in  data step than in sql.

Here is my best effort tested on a 8 character contract number for 6000 contracts (which exceeds that macro variable limit)

data holdi_1;

   do row=1 to 6000;

      contract = put( ranuni(1)* 1e8, z8. );

      output;

   end;

   stop;

run;

proc sort nodupkey;

   by contract;

run;

data _null_;

   mVars +1;                            *** contract counter ;

   len = 0;                             *** amount of macro variable text used so far ;

   length str $100;                     *** to hold quoted contract number  ;

   ***  we are going to load as many &contractNNN as needed ;

   call execute( cats( '%nrstr( %%let contract)', mVars , '=' ) );

   do WHILE( NOT eof );                 *** and will LEAVE once macro var is filled ;

      set work.holdi_1(keep= contract) end= eof;

      have+1;                           *** just for infor report, count rows read ;

      str = quote(trim( contract ));

      call execute( trim(str) !!', ' ); *** adding another contract number to the %let statement ;

      len + ( 3 + length(str) );        *** update amount of macro var used ;;

      if EOF OR len gT 65500 then LEAVE;

   end;

   call execute( trim(str) !!' ; ' );   *** complete the %let statement ;

   putlog 'info: ' have= 'in ' mVars=;

   if eof then

      do;

         call symputx( 'n_contract_vars', mVars );

         stop;

      end;

run;

%put have &n_contract_vars contract variables invoke with %nrstr(&contract1, &contract2, ... );

Frequent Learner
Posts: 1

Re: The length of the value of the macro variable CONTRACT1 (65540) exceeds the maximum length (6553

It might also be easier to just use a subquery or an inline view in the where clause to subset on those identifiers.

Ask a Question
Discussion stats
  • 6 replies
  • 4426 views
  • 1 like
  • 4 in conversation