Calling macro inside of macros, string limit length

[Anotherdream]

Hello. I have been tasked with creating an Exception Management system from within Base SAS.  This produces all kinds of unique problems that i'm not yet aware of how to deal with, but I am running into the specific problem given below.  I have two distinct questions that I hope someone can answer! First I will explain the problem and then hopefully it will make more sense what problems I am running into!

Okay so basically our data has multiple levels, the primary one being Client. We have many exceptions, of which only specific runs are run by specific clients. I would like to dynamically build "if client = A  then do ;   If Category=1 and Balance< 500000 then do ;  ExceptionA="true"; end; end; " type of logic for every single client and exception category.

I want to try to build a mapping table that shows which clients run which exceptions (can even be an excel file) that the business can go into and change the exceptions run on each client at any point in time.

The problem I know I will run into is that the code will be much longer than 100,000 string length, as we have over 5,000 exceptions and 1,000 + distinct clients.

I don't think we could include this into one macro string because of the length limit by definition... If I define a macro string that itself is made of 100 other macro strings, the length restriction still applies to the "master macro".

Question 1)

My question then becomes, how would I go about building one datastep that has dynamic If then else logic, given that the string length of the entire datastep could be 2-3 millions bytes in lenght, with easily 10,000 + lines of code.

Question 2 Specifics). Would it be possible to write the strings in loops into something like a text file, and then read in the text file and put ALL of its data into a data step (that way we bypass macro variables entirely?)...

Aka could we have a text file that looks like following.

If  Client="brandon" then do;

     if balance > 100 and loantype="purchase" and age<50 then do;

          exceptioncode='3535asdf';

          exceptiongrade='B';

          ExceptionAge="12 Months";

     end;

end;

else  Client="Enron" then do;

     if balance > 580 and loantype="purchase" and age<50 and producttype='test type' then do;

          exceptioncode='3535asdf';

          exceptiongrade='B';

          ExceptionAge="12 Months";

     end;

end;

And could we somehow read this into sas and put THIS entire string above into a datastep? something like below.

data exceptions;

set basedata;

CALL TXT FILE?

run;

Thank you very much for your help!

[Tom]

If the code is relatively static then just use %INCLUDE.

If it is dynamic is some way you can use a program write the code to a text file and use %INCLUDE to run it?

filename code temp;

... run some data step code to generate the lines of code. Possibly just copying it from a dataset.

...

%inc code / source2 ;

[Anotherdream]

I'm not sure how I would write the code to the text file and append new versions of the code onto itself.

Example how would I dynamically create code in loops (say I want the code I did above, but each of the "else if" is created in a loop) that would append to the end of a txt file over and over again?

I have zero experience writing to txt files, and using them in include statements.

[Tom]

Code generation is simple once you determine the logic and the data structure to use to drive it.

For example for you little example you might do something like this.  In this case I have left the condition for the IF () statement as just free text in the metadata that drives the code creation.

data rules ;

  infile cards dsd dlm='|';

  length exceptioncode $10 exceptiongrade $1 exceptionage $20 ;

  length client $20 rule $1000 ;

  input exceptioncode -- rule ;

cards;

3535asdf|B|12 Months|Brandon|balance > 100 and loantype="purchase" and age<50

3535asdf|B|12 Months|Enron|balance > 580 and loantype="purchase" and age<50 and producttype='test type'

run;

filename code temp ;

data _null_;

set rules end=eof;

file code lrecl=2000 ;

by client ;

if _n_=1 then put 'select client;';

if first.client then put '  when (' client :$quote. ') then do;' ;

put @6 'if (' rule ') then do;'

   / @6 '  ' exceptioncode= :$quote. ';'

   / @6 '  ' exceptiongrade= :$quote. ';'

   / @6 '  ' exceptionage= :$quote. ';'

   / @6 'end;'

;

if last.client then put '  end;';

if eof then put 'end;';

run;

Which would generate the following code:

select client;

  when ("Brandon" ) then do;

     if (balance > 100 and loantype="purchase" and age<50 ) then do;

       exceptioncode="3535asdf" ;

       exceptiongrade="B" ;

       exceptionage="12 Months" ;

        end;

  end;

  when ("Enron" ) then do;

     if (balance > 580 and loantype="purchase" and age<50 and producttype='test type' ) then do;

       exceptioncode="3535asdf" ;

       exceptiongrade="B" ;

       exceptionage="12 Months" ;

     end;

  end;

end;

[Anotherdream]

Humm that's exactly what i'm looking for. I guess my next logical question is, I would run this by simply usign the %include and then the name of the file?

You don't have a datastep or anything in the code, so could you do something like.

data answer;

set mydata;

%inc code / source2 ;

run;

?

[Tom]

Yes.

Sometimes  in such a system you might have the code generation and the code that uses it as separate programs.  So you only need to re-generate the code when the rules change, but you might run it many times against constantly updating data files.  In that case write to a permanent file instead using the TEMP engine to generate a temporary file.

[Anotherdream]

That's a very good idea... I am curious, If I wrapped the txt file call inside of a macro, would you get the problem that you get in the sas compiler where if you have your code strings too long, the macro will cut off the varaibles at the (256 th?) digit?

What i mean is, if I had a code that did this.

data rules1;

set rules;

if upcase(clientname)="REDWOOD TRUST" and upcase(clientProjectName)=''REDWOOD TRUST-FLOW'' and OriginalPrincipalBalance<=1000000 and OriginalPrincipalBalance > 0 and upcase(LoanPurposeMUNI)="CASHOUT REFI" and upcase(OccupancyMUNI)="PRIMARY" and index(upcase(NumberofUnitsMUNI),''ONE'')>=1 and index(upcase(ProductTypeMUNI),''ARM'')>=1 and CLTV > 75;

run;

SAS would actually fail since the IF logic is too long (if wrapped in a macro, works in non macro code). 

But if I wrote this to a txt file and read it in, I am curious of what restrictions exist! I will do some more testing, but if you had a paper you could reference me too that would be extremely helpful!

Now if I can figure out how to modify your code to allow for multiple exceptions per client, this is actually a pretty nifty proof of concept for an exception management system, managed through Excel Input sheets!

Brandon

[Tom]

There is not any limit to length of a statement or length code generated by a macro.  Did you try it?  Perhaps you tried to storing the code into a macro variable?  One macro variable can only hold about 32K characters.  Also there are memory limits on total space for macro variable storage.  So if you tried to generate a macro variable "array"  (RULE1, RULE2, ....) then you might run out of memory.

The code I posted already handles multiple records in the RULES table per client.  But I am not sure what you want to happen when there are multiple matches.  Perhaps you just want to add an OUTPUT statement inside of the IF-THEN DO loop ?

[Reeza]

Are you stuck with this method? I question if this is the best solution to your problem.

If you have control tables, it seems like there may be better methods to generate exceptions.

[Anotherdream]

Tom! Yep what I meant was how to handle the set up of the code for maximum efficiency given the multiple levels within the code.

Example, each client can have X different exceptions, and each exception can have Y different clients. Furthermore a group of exceptions could have the same "grouping" attributes and only differ on one attribute.

Example):

Exception 1 could have "first lien", "fixed", "purchase", "Occupied"  as 4 of the 5 attributes, and it could share those 4 attributes with 1,000 + other exceptions. As such, those 4 attributes would make perfect sense in the first level of a sub-setting if clause. Something like.

If lien="first" and producttype="Fixed" and Purpose="Purchase" and status="Occupied" then do;

     if client='ABC' then do;

          if ltv > 80 then do;

               Exceptioncode='ABC';

               ExceptionGrade=3;

               output;

          end;

         if income < 30000 then do;

               ExceptionCode='123';

               Exceptiongrade=1;

               output;

          end;

           /*Note the IF above is correct. These are two independent exceptions, both of which need to be able to trigger on the appropriate client level*/

     end;

     else  if client='ABC' then do;

          if ltv > 90 then do;

               Exceptioncode='ABC';

               ExceptionGrade=3;

               output;

          end;

     end;

end;

Now this structure is more efficient than only 1 level of "client" in the IF-then else structure, however there is no way to know what the primary attributes are up-front and each new exception can change that structure, so I think going with client only as the primary level would be the most 'general efficient' methodology for now.

Reeza I am not AT ALL stuck with this method. Knowing  NOTHING about exception flagging this is what I logically thought would work.  Some background for you. I am running SAS on a local machine, and have nothing to do with the generation of the data. I am picking up the data after it is created from an application, and then running it through SAS to output exceptions.  That could change in the future and SAS could be used to interface with the application directly, which I would imply would allow me to use control tables!

Could you perhaps give a link to some examples of the control table structure you are referring too? I'm not even really sure where to begin

Thanks!

Brandon

[Reeza]

Begin by explaining and detailing your problem thoroughly

I have a good idea of what you need, but hate making assumptions.

[Anotherdream]

Well outside of what I have already said the problem is a bit hard to explain, but I will do my best.

I have been given 300 different exceptions and the logic associated to trigger each one. I have been asked to write a code that will pull from a sql database and run these exceptions against the sql database loan level data.

The problem becomes more difficult because I have been told there are over 8,000 + additional exceptions that will be coming, in no logical order.

In addition, there are attributes in the data that can control the flow of the exceptions. Ex: Right now the attribute "client" allows specific exceptions to be run, and others to not be run.  This implies there are global exceptions, and client specific exceptions. I need to design a process that will incorporate both of these, and also allow users to change which exceptions run on which client at a given point in time.

However even this control flow can (and likely will) change. Ex: Tomorrow the combination of Client and policy review level might control which exceptions are run, etc..

Furthermore an exception can AND DOES have minor changes by these attributes. Example the Exception of "LTV too high" could be the same on every field across clients, except the LTV portion... Please see the example right below.

Client 'Brandon'

LTV >=80;

Client 'Karl'

Ltv>=90;

etc..

I was planning on handling this by making two exceptions and only running the appropriate one per client... There are a lot of ways of doing this however and i'm not convinced that is the 'most correct' methodology.

The problem is compounded because I am not yet sure how to handle the situation of non-complete exception logic.  Again see the example below for a non-complete exception example.

Loan Purpose='Purchase'

ltv>=90

Product Type = 'Fixed';

By definition the above criteria is missing Arm loans, and all loan purposes who are not purchase. HOWEVER there could be another exception given later like below...

Loan Purpose='Purchase'

ltv>=90

Product Type = Arm;

Which covers the arm problem, so I am not yet sure how to  encorporate all exceptions and then check for missing criteria.

So really the majority of the job is an "exception architecture" instead of the actual coding of the exceptions... I could always just code independent if statements for EVERY exception, and then drop the exceptions that I don't want at the attribute levels that they're not needed at (client and policy review for example).  However since I will be running thousands of exceptions every time a loan is completed this is not efficient enough, hence why I asked the question to dynamically create the If - then statements...  Also the independent if then statements will be extremely hard to de-bug, etc.. if there are over 9,000 of them.

I hope that is in depth enough, please let me know if you have any clarifying questions!

[RW9]

Hi,

Just want to chime in here and say that writing to a temporary file is not necessary.  You can utilize call execute to do exactly the same thing, using your rules dataset as the loop, and a proc sql insert statement to insert fallouts into a master table.  (Pinching your test data Tom, hope you don't mind):

data rules ;
infile cards dsd dlm='|';
  length exceptioncode $10 exceptiongrade $1 exceptionage $20 ;
  length client $20 rule $1000 ;
  input exceptioncode -- rule ;
cards;
3535asdf|B|12 Months|Brandon|balance > 100 and loantype="purchase" and age<50
3535asdf|B|12 Months|Enron|balance > 580 and loantype="purchase" and age<50 and producttype='test type'
run;

proc sql;
  create table EXCEPTIONS
  (
    EXCEPTION_CODE char(200),
    NAME char(200)
  );
quit;

data _null_;
  set rules;
  call execute('proc sql;
                  insert into EXCEPTIONS
                  select  "'||strip(exceptioncode)||'",
                          NAME
                  from    YOUR_DB_DATA
                  where   '||strip(exceptionrule)';
                quit;');
run;

Its pretty much how OC does things, except there it is a cursor, here its a dataset.  So the rules are passed into the dataset.  The call execute generates the sql code including the rule, that is run after the datastep completes.

[Anotherdream]

Hey RW9! I will take a peek into your example, thank you 😃

To clarify, what is OC? You say "pretty muc hhow OC does things".

Thanks

[RW9]

Ah, it is Oracle Clinical.  I am leaving for the day now, but can pick up further question tomorrow.