topic Re: Great PROG now useless after hitting 65534 limit in New SAS User

Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 13 Nov 2019 01:05:09 GMT

I had a great prog running in SAS 9.3 for years that substituted a series of quoted strings in a changelist into another program, ran a regression analysis, appended the results to a datafile, and repeated that process for the next string.

The strings are various combinations of subsets of a main database. In a nutshell, the program tests which combinations of subsets give the best (or worst) results.

It worked great with a small string length and limited number of strings.

I greatly expanded the number of strings (from 1,000 to millions) and quickly got this message:

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

What alternatives can I try to get around this limitation?

Thoughts would be appreciated.

Re: Great PROG now useless after hitting 65534 limit

Kurt_Bremser — Tue, 22 Oct 2019 06:01:06 GMT

Store your changelist in a dataset, and use any join method to work with subsets.

Also note that the subsetting if you used will have worse performance than using a where condition.

Re: Great PROG now useless after hitting 65534 limit

Kurt_Bremser — Tue, 22 Oct 2019 06:22:15 GMT

PS a GREAT program would be written in a way that scales until you simply run out of computing resources like disk space or RAM.

It is never a sustainable solution to store mass data in macro variables. For this, SAS provides datasets.

Re: Great PROG now useless after hitting 65534 limit

ballardw — Tue, 22 Oct 2019 14:32:57 GMT

Assuming you construct a data set with a variable named Iso that holds the values you want to keep then that data step could be replaced with

proc sql;
   create table data as
   select b.*
   from datasetwithchangelistvalues as a
        left join
        data1 as b
        on a.iso = b.iso
   where not missing(b.iso)
   ;
run;

And unless you have a terribly feeble computer won't puke on several million values of ISO

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 13 Nov 2019 01:05:44 GMT

Thanks ballardw . And yes, ISO is a field in Data1 and I can certainly call in ISO in your "datasetwithchangelistvalues". But, I am just using the ISO field as a selector to cull the data I want to run through the regression loop (ISO is just one of about 30 variables by a few thousand observations). I don't understand - probably because I have a limited knowledge. How does your suggested code result in the quoted changelist string created in the %macro test; which then gets inserted, one at a time, followed by a regression analysis, and then the regression analysis gets looped for the dataset created by culling the ISOs in the next quoted string value in changelist? Or are you suggesting that the solution is to abandon the entire %macro test; as well as the start of the %MACRO TARGET_ANALYSIS;?

Re: Great PROG now useless after hitting 65534 limit

Tom — Tue, 22 Oct 2019 19:19:40 GMT

Just do the subsetting differently.

So change the macro TARGET_ANALYSIS to NOT do this:

DATA REGDATA;
  SET REGDATA1;
  IF ISO IN (%unquote(&val));
RUN;

And instead do something that is scalable.

Note that it is possible to create a macro (not a macro variable) that would emit a list of values from a dataset. But it is probably gong to be easier to just fix the places where you need to do the subsetting to use the dataset directly instead.

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Tue, 22 Oct 2019 19:27:31 GMT

Thanks Tom. I have no idea what you just said. Subsetting, scalable are meaningless terms to me. I am a novice.

Re: Great PROG now useless after hitting 65534 limit

Tom — Tue, 22 Oct 2019 20:21:04 GMT

Those are just English words and mean what they sound like.

You are currently trying to stuff a lot of information into a macro variable. You have found out that macro variables only hold 64K bytes. So your solution didn't scale. As the amount of data grew the program could not handle it.

So change to using a DATASET to store the information. Take your %LET statement

%LET changelist 
='1','2','3','4','5'
|'1','2','3','4','6'
|'1','2','3','4','7'
;

and change it into a DATA step.

So if the variable ISO is 10 characters long then the data step might look like this:

data changelist ;
  length list_number 8 ISO $10 ;
  list_number +1 ;
  infile cards truncover ;
  do until (iso=' ');
    input iso @;
   if iso ne ' ' then output;
  end;
cards;
1 2 3 4 5
1 2 3 4 5 6 7
1 2 3 4 
;

Then change your method for selecting what data to use to be based on the DATASET instead of the macro variable.

So the subset just of the first set of ISO values you could use:

...
proc sql ;
  create table REGDATA as 
    select *
    from regdata1
    where iso in (select iso from changelist where list_number = 1)
  ;
quit;

Now if you want to select different subsets of the REGDATA you just need to change which value of LIST_NUMBER you select.

You could use the macro variable I that you had before in place of the hardcoded 1 in the code just above.

Note you might be able to eliminate the macro completely and just change your other steps use LIST_NUMBER as a BY variable in their processing.

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 13 Nov 2019 01:06:53 GMT

Tom: thanks. The 1st part (creating the dataset of changelist values) works like a charm.

Here is the code I used that works great:

However, the 2nd part of your suggest code, creating a subset from the larger dataset, failed. Zero were selected.

Perhaps I was not clear, there is no ISO value of 1 2 3 4 or

1 2 3 4 5 6 7. Those are, in that case, four individual ISO values: an ISO of 1, of 2, of 3, and of 4. So the database currently has 10-years of data for each ISO and, there are 38 ISOs (a total of 380 lines).

In reality, the ISO is a 3-character string representing countries within the larger database. For example:

'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','ESP','SWE','CHE','USA','GBR'

So when the line

where iso in (select iso from changelist where list_number = 1)

executes, the program should populated it with

In fact, I did it manually and the SQL code you suggested works great. I am calling that a partial solution.The REGDATA just created runs through a regression analysis for that particular combination of countries. I am calling that a partial solution.

However, that is one loop. 2nd loop uses a different combination of countries in the "where iso in" statement (i.e., the string in the 2nd observation in the changelist).

etc, etc until the last combination in the changelist is run. There are millions of combinations, so I can't do that manually.

The sticking point in the program now is to tell SAS to fill in that "where" statement automatically and run the regression with each subsequent combination.... millions of times without hitting that 65534 macro limit.

Re: Great PROG now useless after hitting 65534 limit

ballardw — Tue, 22 Oct 2019 23:16:51 GMT

An example using a data set you should have access to.

First create a data set of the variables whose values you want to keep (or exclude about the same difficulty).

This data set will contain a value that I know does not actually exist in the data set I am going to extract from:

data work.names;
    length name  $ 8;
    input name;
datalines;
Alice
Judy
Henry
Jacques
;
run;

Now use proc sql to find the values that have matching names.

data work.names;
    length name  $ 8;
    input name;
datalines;
Alice
Judy
Henry
Jacques
;
run;

If you look at the data set work.want you will find that only records with matching values of Name are in the output.

Which is exactly what your example no longer working data step:

DATA DATA;
SET DATA1;
IF ISO IN (%unquote(&val));
RUN;

does. I admit that Proc Sql is likely to change the order of the records but since your current code doesn't work at all I think that might be a small trade off.

This is one exactly one of the methods to get around the limitation of the macro "list" approach. The data set work.names could contain millions of records.

If there is something related to what goes on inside the macro TARGETANALYSIS then you have to share the code for that macro as saying an approach does not work with out details as to why is pretty pointless.

If you need to call some code for every value then CALL Execute with the data set will do that as well.

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 23 Oct 2019 01:28:00 GMT

"If you need to call some code for every value then CALL Execute with the data set will do that as well."

ballardw, Thanks. I think that sums up the situation as of now. I am interested in understanding the Call execute function.

I may have been unclear in originally explaining the goal and function of my original programming. I have a database with data for 35 countries. Each country is identified by a 3-character field (ISO). For example 'DEU' is Germany. I want to run a regression analysis of all countries, but I also want to determine if I get a better result using only a subset of the 35. Lets say I want to test all combinations of 28 countries from within the master dataset of 35. That is 6.7 million possible combinations (order does not matter). Today, I created a program that generates all 6.7 million combinations as a text string. For example:

'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN',

'FIN','FRA','DEU','NZL','NOR'

My program generating the combination strings is equivalent to your first datastep and, the text string above, would be one row in the

data work.names;

Using your example, I can manually take each of those rows in data.names and insert that string into the PROC SQL, or another one of the suggested datasteps, or my original program in place of the (%unquote(&val);

Doing so will create a subset of data, that runs through the regression analysis. The results are captured in an appending database. That is one loop. I am good on programming all of those steps. However, I can't manually enter the string of combinations 6.7 million times and, my old macro-based change list capped out after just a few hundred lines of those long character string combinations.

The only thing going on in the TARGET_ANALYSIS is 1) creating the subset, 2) regression analysis, 3) creating a database of results, which includes the combination that generated each result for easy identification. In this last step, the %unquote(&val) is invoked again, so that it gets recorded along with the result.

Call Execute is interesting and it may be exactly what I need. Can you tell me more about how I could use call execute to generate the character string values for %unquote(&val) one at a time (followed by the regression analysis) and looping back for each of the 6.7 million rows in the list of combinations? Thanks

Re: Great PROG now useless after hitting 65534 limit

Kurt_Bremser — Wed, 23 Oct 2019 06:14:13 GMT

@texasmfp wrote:
Thanks Tom. I have no idea what you just said. Subsetting, scalable are meaningless terms to me. I am a novice.

Subsetting: taking a part out of a greater amount of data, which may be variables within an observation, or a dataset. In SAS parlance, "subsetting" is often used in "subsetting if", which is this construct:

if <condition>;

an "if" statement without a "then". It means that if the condition is not met, the rest of the data step iteration (including any output) is skipped. This is used similar to a where condition, but can use variables created in the data step.

Scalable: means that a process will work for any conceivable amount of data, with no consequences except more resource usage and longer run times. In your case, the process did not scale, because you used a very limited tool (64K is really small nowadays) along the way.

Re: Great PROG now useless after hitting 65534 limit

Tom — Wed, 23 Oct 2019 12:56:02 GMT

Instead of making each observation have one value of ISO you have made each observation have a LIST of ISO values.

So you have changed your limit from 64K to the full list of lists to 32K for any single list.

If you will never have a list of ISO values that is longer than 32K then your method might work with less modifications to your original program. Instead of using %SCAN() to pull one list from the larger list of list use CALL SYMPUTX() to push one observation from your dataset.

data _null_;
  set changelist ;
  where list_number = &i ;
  call symputx('changelist',iso);
run;

Then you can continue to use

if iso in (&changelist);

Or, probably more correctly:

where iso in (&changelist);

Otherwise change your data step to read each ISO value, like the code I posted. Just add the DSD option to your INFILE statement.

data changelist ;
  length list_number 8 ISO $280 ;
  list_number +1 ;
  infile cards dsd truncover ;
  do until (iso=' ');
    input iso @;
   if iso ne ' ' then output;
  end;
cards;
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','SVN','ESP','SWE','CHE','USA','GBR'|
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','SVN','ESP','SWE','CHE','USA'|
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','SVN','ESP','SWE','CHE','GBR'|
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','SVN','ESP','SWE','USA','GBR'|
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','SVN','ESP','CHE','USA','GBR'|
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','SVN','SWE','CHE','USA','GBR'|
'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN','FIN','FRA','DEU','NZL','NOR','POL','PRT','RUS','SVK','ESP','SWE','CHE','USA','GBR'|
;

Re: Great PROG now useless after hitting 65534 limit

ballardw — Wed, 23 Oct 2019 16:27:34 GMT

@texasmfp wrote:
"If you need to call some code for every value then CALL Execute with the data set will do that as well."

ballardw, Thanks. I think that sums up the situation as of now. I am interested in understanding the Call execute function.

I may have been unclear in originally explaining the goal and function of my original programming. I have a database with data for 35 countries. Each country is identified by a 3-character field (ISO). For example 'DEU' is Germany. I want to run a regression analysis of all countries, but I also want to determine if I get a better result using only a subset of the 35. Lets say I want to test all combinations of 28 countries from within the master dataset of 35. That is 6.7 million possible combinations (order does not matter). Today, I created a program that generates all 6.7 million combinations as a text string. For example:

'IND','KOR','TUR','AUS','AUT','BRA','CAN','CHL','CHN','CZE','DNK','EST','FIN','FRA','DEU','GRC','HUN','ISL','IDN','IRL','ISR','ITA','JPN',

'FIN','FRA','DEU','NZL','NOR'

My program generating the combination strings is equivalent to your first datastep and, the text string above, would be one row in the
data work.names;
Using your example, I can manually take each of those rows in data.names and insert that string into the PROC SQL, or another one of the suggested datasteps, or my original program in place of the (%unquote(&val);

Doing so will create a subset of data, that runs through the regression analysis. The results are captured in an appending database. That is one loop. I am good on programming all of those steps. However, I can't manually enter the string of combinations 6.7 million times and, my old macro-based change list capped out after just a few hundred lines of those long character string combinations.

The only thing going on in the TARGET_ANALYSIS is 1) creating the subset, 2) regression analysis, 3) creating a database of results, which includes the combination that generated each result for easy identification. In this last step, the %unquote(&val) is invoked again, so that it gets recorded along with the result.

Call Execute is interesting and it may be exactly what I need. Can you tell me more about how I could use call execute to generate the character string values for %unquote(&val) one at a time (followed by the regression analysis) and looping back for each of the 6.7 million rows in the list of combinations? Thanks

Since you say you have code to generate the combinations then show us that.

And we would NOT be creating any macro variable but an actual Where (or IF though where usually is faster) statement like

if Iso in ('IND' 'KOR' 'TUR' 'AUS' 'AUT' 'BRA' );

Here is an example to creates ONE such statement with a data set you should have that selects 3 names from SASHELP.CLASS and prints on those records.

/* these two steps genarate a data set with all the values of the 
   name variable as a single record to demonstrate
*/
Proc sort data=sashelp.class (obs=3) out=work.class;
   by name;
run;

proc transpose data=work.class out=work.trans (drop=_name_)
   prefix=Name
   ;
   var name ;
run;
/* an example of building a where statement for a proc call*/
data junk;
   set work.trans;
   array n Name: ;
   /* string variable to hold the values
   the name variable is 8 characters so need to add 2 for quotes and a space to separate
   3 values (maximum in this case) so 3*(8+2+1) = 33*/
   length nstr $33;
   do _i_=1 to dim(n);
      nstr= catx(' ',nstr,quote(strip(n[_i_])));
   end;
   call execute ("Title 'Names to use are "||nstr||"';");

   call execute ("Proc print data=sashelp.class noobs; where name in (");
   call execute (nstr);
   call execute ("); run;title;");
run;

Depending on exactly how you go about creating your combinations it may be easy or not to modify the above example.

Note two different ways of using the list with concatenating into a string on the fly in the Title statement or referencing alone in the body. The later leaves the where statement open from the previous call execute line and then closes afterward. The Log shows the generated code.

Or instead of call execute you can use PUT statements to write to a file and %include that file.

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 13 Nov 2019 01:09:32 GMT

Tom, thanks. Indeed, the string will likely never exceed 300 characters, let alone 32K, so making minimal changes is attractive. However, when I tried your code I got an error message when SAS tried to evaluate &i. Note that my dataset of combinations is called testcomb2 and the variable is called combos2 rather than iso.

when I run it, I get the following error:

1 data _null_; set testcomb2; where list_number = &i ; call symputx('changelist',combos2); run;

1 ! %US13_TARGET_ANALYSIS;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant,

a datetime constant, a missing value, INPUT, PUT.

NOTE: Line generated by the invoked macro "TEST".

1 data _null_; set testcomb2; where list_number = &i ; call symputx('changelist',combos2); run;

1 ! %US13_TARGET_ANALYSIS;

ERROR 76-322: Syntax error, statement will be ignored.

WARNING: Apparent symbolic reference I not resolved.

MPRINT(TEST): where list_number = &i ;

ERROR: Syntax error while parsing WHERE clause.

MPRINT(TEST): call symputx('changelist',combos2);

MPRINT(TEST): run;

Re: Great PROG now useless after hitting 65534 limit

Tom — Wed, 23 Oct 2019 19:47:19 GMT

In your previous code the %SCAN() function call was nested inside a %DO I= macro loop. The code you just posted is missing that loop.

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 13 Nov 2019 01:07:48 GMT

almost there, it looks like the counter step is not functioning. Note that I dropped the "|" stop delimiter from the character string, since we moved to a dataset.

I get this error message:

13341 %test

MLOGIC(TEST): Beginning execution.

MLOGIC(TEST): %LET (variable name is N)

WARNING: Apparent symbolic reference CHANGELIST not resolved.

SYMBOLGEN: Macro variable N resolves to 0

MLOGIC(TEST): %DO loop beginning; index variable I; start value is 1; stop value is 0; by value is 1. Loop will not

be executed.

MPRINT(TEST): */

MLOGIC(TEST): Ending execution.

Re: Great PROG now useless after hitting 65534 limit

Tom — Wed, 23 Oct 2019 20:03:50 GMT

So before you where counting the number of lists in your list of lists.

Now you need to count the number of observations in your dataset of lists.

proc sql noprint;
 select count(*) into :n
  from testcomb2
 ;
quit;

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 23 Oct 2019 20:30:44 GMT

YAHTZEE!!!! Much thanks Tom.

Re: Great PROG now useless after hitting 65534 limit

texasmfp — Wed, 23 Oct 2019 22:11:49 GMT

Just ran 6546 combinations in 41 minutes (need a faster computer!). I learned a lot from this discussion.

Thanks to all.