creating macro variable which length exceeds the default available length over 32000 bytes

[buddha_d]

Dear community,

              Could you please help me to solve this macro issue. I have a large account numbers dataset and when I tried to get in a macro to optimize my query it is throwing off error (length exceeds default length). So I tried to create different datasets based on ending value of the account number (which is alpha numberial value). But, I have issues doing it. I can do it for an individual dataset. The below code is sample dataset.

data new;
input number$; 
lastnum=substr(number,length(number));
cards;
1000 
2000
3000
1111
1221
3331
22
42
62
222
6933
253
653
263
854
984
1044
1115
985
1785
;
run;

PROC SQL ;
	create table new1 as 
	SELECT DISTINCT (LASTNUM) 

	FROM NEW; QUIT;

proc sort data=new1;
	by lastnum;
run;

DATA _NULL_;
	SET NEW1 END=EOF;
	BY lastNUM;
	X+1;
	IF FIRST.lastNUM THEN DO;
	CALL SyMPUTX('DSN'||COMPRESS(PUT(X,2.)),lastnum);
	end;
	if eof then do;
	call symputx('tot',_n_);
	end;
run;
%put &dsn1**&dsn2**&dsn3**&dsn4**&dsn5**&tot;
%macro test;
%do i=1 %to &tot;
PROC SQL NOPRINT;
	SELECT "'"||STRIP(NUMBER)||"'" INTO: NUM&i. SEPARATED BY ","
	FROM NEW 
	WHERE LASTNUM="&&dsn&i.";QUIT;
%end;
%mend;
%test;

%put &num1****&num6;

************************* below query works perfectly, but can't apply to all the 6 macro variables;
proc sql;
	select "'"||strip(number)||"'" into:number separated by ','
	from new
	where lastnum="0";
quit;

The desired macro strings should look like this:

num1---->'1000','2000','3000' (&num1 should have these values)
num2---->'1111','1221','3331'
num3---->'22','42','62','222'
num4---->'6933','253','653','263'
num5---->'854','984','1044'
num6---->'1115','985','1785'

[Tom]

What is the actual SAS code you are trying to use macro code and macro variables to create?

What is the purpose of the code? Is there another way to do it?

Are you actually trying to split the data based on the last digit in the string?  Or are you just doing that to try to make the lists shorter so they are longer than a single variable can hold (32,767 bytes) or a single macro variable can hold (two times that)?

If for some reason (what exactly is harder to tell) you really wanted to put thousands of numbers into macro variables here is a way that should make it possible. Place the values into macro variables and then place references to all of them in another macro variable.  Then use that macro variable where you want to list generated as code.

Here is an example. Let's use 200 as the limit for how long a string we can fit into one macro variable. And use 10 as the longest any individual word might be.  First let's make some dummy data.

%let max_len=200;
%let max_word=10;

data have;
  input name :$&max_word.. @@;
cards;
Alfred Alice 
100002 100003 100004 100005 100006 100007
200002 200003 200004 200005 200006 200007
300002 300003 300004 300005 300006 300007
400002 400003 400004 400005 400006 400007
;

Now let's use a data step to read the data and concatenate the quoted version into a data step variable.  Keep adding them in until it gets too long for any more.  Write it to a macro variable.  And append a reference to the new macro variable to the end of target main variable.

Where is an example that makes variables named MVAR1,MVAR2,.... and ALL.

data _null_;
  if _n_=1 then call symputx('all',' ');
  length string $&max_len ;
  suffix+1;
  do until((length(string)> (&max_len - &max_word -3)) or eof);
    set have end=eof;
    string=catx(',',string,quote(trim(name),"'"));
  end;
  call symputx(cats('mvar',suffix),string);
  call symputx('all',catx(',',symget('all'),cats('&mvar',suffix)));
run;

Let's look and see what we created. First use %SUPERQ() to see what the macro variable ALL really looks like. Then see what it resolves to:

2256  %put %superq(all);
&mvar1,&mvar2
2257  %put &=all ;
ALL='Alfred','Alice','100002','100003','100004','100005','100006','100007','200002','200003','2000
04','200005','200006','200007','300002','300003','300004','300005','300006','300007','400002','400
003','400004','400005','400006','400007'

Here is an example of how you might use such a value in code:

2259  data test;
2260    set sashelp.class ;
2261    where name in (&all);
2262  run;

NOTE: There were 2 observations read from the data set SASHELP.CLASS.
      WHERE name in ('100002', '100003', '100004', '100005', '100006', '100007', '200002',
      '200003', '200004', '200005', '200006', '200007', '300002', '300003', '300004', '300005',
      '300006', '300007', '400002', '400003', '400004', '400005', '400006', '400007', 'Alfred',
      'Alice');
NOTE: The data set WORK.TEST has 2 observations and 5 variables.

[buddha_d]

Tom,

       Thank you for replying. 

What is the actual SAS code you are trying to use macro code and macro variables to create?

I mentioned my code and I was trying to create a logic that clearly didn't work.

What is the purpose of the code? Is there another way to do it?

The purpose of this macro code is to keep all the account numbers in a macro and run it against Oracle table to find the matching records. 

Are you actually trying to split the data based on the last digit in the string?  Or are you just doing that to try to make the lists shorter so they are longer than a single variable can hold (32,767 bytes) or a single macro variable can hold (two times that)?

Yes, I was trying to split the data based on my last digit of the string. 

I am trying to save the account numbers in macro variable and run the query in SAS to access DBMS (oracle) table to get the matching account numbers. There are about 50000 account numbers and I have over 500 million records (db table) to get the matching records. My whole point is to get less macro variables to hold all the account numbers to query against the DB Table.

  I ran the logic you mentioned in my SAS and I am getting the following errors 

ERROR: The text expression length (65538) exceeds maximum length (65534). The text expression has been truncated to 65534 characters.

Any thoughts on fixing these please? 

Thanks again.

[Tom]

You need to say what part of the code is generating that error.

You should not have any text expression that long.  I would consider only the quoted account numbers as text expressions.  Perhaps that is Oracle's way of saying your query is too long?

Make sure to use a length more like 32767 for each macro variable string and probably 53 or so for each account number instead of the 200 and 10 numbers I used in my example.

You could also still add the split by last digit if you want and just do the step 10 times, once for each last digit.  

[buddha_d]

This might work for smaller length strings, but mine are long and cut in between the string. But, I will accept it as now, but there is no other option is being listed from yesterday.

[Kurt_Bremser]

It is rarely a good idea to keep large amounts of data in macro variables. What do you want to achieve with your code in the end?

[buddha_d]

KurtBremser,

I am trying to save the account numbers in macro variable and run the query in SAS to access DBMS (oracle) table to get the matching account numbers. There are about 50000 account numbers and I have over 500 million records (db table) to get the matching records. My whole point is to get less macro variables to hold all the account numbers to query against the DB Table. My each account number field length is 200 and i have about 50 K account numbers. 

Thanks

[Kurt_Bremser]

One simply does not do that. Create a table with your account numbers, upload it to a temporary table in Oracle, and use it in explicit passthrough there (e, g. create a view) to do the subsetting.

Maxim 14: "Use the Right Tool" is also valid for communication with databases.

Data belongs in tables/datasets.

PS Are your account numbers actually 200 characters long?

[buddha_d]

Thanks Kurt. 

             I agree that it works really good creating temporary table and using pass through. Unfortunately, data belongs to other department and I don't have permissions to do that. The field length allocated for account number is 200. But, when I queries for the maximum actual length is 50. This is the reason for creating the macro. 

[Tom]

---

@buddha_d wrote:

KurtBremser,

I am trying to save the account numbers in macro variable and run the query in SAS to access DBMS (oracle) table to get the matching account numbers. There are about 50000 account numbers and I have over 500 million records (db table) to get the matching records. My whole point is to get less macro variables to hold all the account numbers to query against the DB Table. My each account number field length is 200 and i have about 50 K account numbers. 

Thanks

---

Let's do a little arithmetic. 50,000 numbers times 53 bytes per number divided by 32,760 bytes per macro variable list yields just 81.  You could put all 50K account numbers into 81 macro variables. You will not have any trouble putting references to all 81 macro variables into the 82nd macro variable since that will only take 2,754 bytes.

But I am not sure if the Oracle database is going to like running a query that is over 2.5 million bytes long.

[Kurt_Bremser]

---

@Tom wrote:

---

But I am not sure if the Oracle database is going to like running a query that is over 2.5 million bytes long.

---

It won't: https://docs.oracle.com/cd/E11882_01/timesten.112/e21643/limit.htm#TTREF456. The maximum size of an SQL statement is 409.600 bytes.

Data belongs in tables, PERIOD. And if that is not possible at the moment, it has to be enabled.

Theres's simply too many Mordac's around.

[buddha_d]

Thanks KurtBremser for your help and others as well.