Calling macro inside of macros, string limit length

[Anotherdream]

Reeza when you say Control tables, are you referring to something like attached in the following Excel sheet? (I assume you are).

The more I think about your control table suggestion the more I like the idea of it actually.  I will do some experimenting on if this is a 'logical' way to go forward with these tables and see if I can build code that will dynamically write code based upon their design (mapping the columns into condition rows, etc..)

The reason I like your control table idea (if anyone cares) are below.

If you notice in the attached sheet, I have only 2 exceptions by 2 clients, however their conditions are distinct per client. So really it is 4 distinct exceptions.  The problem I have is I will have 7,000 + exceptions, for over 100+ different clients, and each client WILL be slightly different per exception.

As such I'm not sure if its best to map client into the exception like I have done, or create seperate tables and simply build the exceptions at run time. (I think i'm going to have to build the exceptions at run time per the clients that are found at this point). 

The secondary (MAIN PROBLEM)  is how do I handle adding new exceptions in this process, or massive global changes to exceptions?  I do like how control tables are a bit easier to work through for human eyes... I also imagine I could write code that would dynamiaclly loop through all of the control tables and look for particular exceptions to make changes... thus add a new row logic to every exception that has a specific output.... 

Oh Reeza, if this is NOT what a control table is (I honestly don't know what that is) please let me know!

[Reeza]

Hi Brandon,

That's not what I had in mind. I'd use a control table to set up the criteria's which may be what you were trying to do in the first place.

If your string becomes too long to include in a macro program you can add the "/lrecl=256;" option to your include statement so that it will run as well. Or as long as it needs to be, I'm not sure what the limit is.

ClientID ConditionID Variable Condition ExceptionType

A        1    LoanType    =ARM      Exclude

A        1    LoanAmount   >180    Exclude

Becomes

if ClientID = "A" and loanType="ARM" and LoanAmount>180 then exceptionType="Exclude"

This is used to generate your code, with a unique condition generated by clientID condition so they can be strung together with an AND in the clauses.

Hope that helps at least a little.

Fareeza

[RichardinOz]

I was faced with a similar requirement in student enrolment data, where hundreds of exceptions had to be monitored and the list of exceptions potentially could be enlarged from year to year.  I found it best to break the problem down into more manageable chunks, using SQL or datastep code as best fitted the type of exceptions.  Examples:

1. Null data in a field.  A data _null_ step (or a macro) could be used to generate from a list multiple statements of the type

     If missing(abc) then do ; Exceptioncode = 'xyz' ; output ; end ;

    A following datastep could then screen for all null exceptions.  Not suitable for conditions involving multiple nulls.

2. Generic range errors - check for non client specific out of range exceptions using formats created from the exception specifications - useful for identifying values above a maximum, or zero, or negative, or not in a list of specific values.  A data _Null_ step could create a dynamic list of exceptions of the type

     Exceptioncode = Put(abc, xyz.) ; if not missing (Exceptioncode) then output ;

3. More complex conditions - if you have a relatively small number of clients with a relatively large list of specific exceptions it may pay to separate them out using 

     Where client = 'abc' ;

4. Otherwise create the if statements for complex conditions in another data _null_ statement.

I quite like the idea of generating the multiple If statements in an external file and then using %include to process them.  It creates persistent, viewable and auditable lists of conditions that have been applied.

Data Exceptions ;

     Set rdbms.customer_info ;

     %Include "Null_exceptions.txt" ;

     %Include "Range_exceptions.txt" ;

     %Include "Complex_exceptions.txt" ;

     Keep id, exceptioncode, .... ;

Run;

Data ABC_Exceptions ;

     Set rdbms.customer_info (where = (client = 'ABC')) ;

     %Include "ABC_exceptions.txt" ;

     Keep id, exceptioncode, .... ;

Run ;

Proc Append Base = Exceptions

                    Data = ABC_Exceptions

                    ;

Run ;

Richard.

[Anotherdream]

Hiya Richard. I agree that I do like the idea of creating the txt files for audit purposes.

In our example we have hundreds (200+) of clients, with thousands of exceptions across clients.

The big problem I have is the unknown... If tomorrow they come and say "all 10,000 exceptions need to have an additional condition to only trigger if Field "XYZ" is equal to "correct".  I could run al 10,000 exceptions and then subset the results by that field bein "correct", or I could have a master If statement that would only run WHEN that field is correct.

Option 2 is the "best" way to do it in my opinion from a coding standpoint, but it becomes very difficult to set up a table structure that will allow for multiple If then statements within the code (also should we really?).  I will continue to play around with the code more to determine if I want to run all exceptions and then sub-set the results, or if I want to try to code them into the IF statements....

[Anotherdream]

Reeza , did my example give enough information for you to elaborate more on the control table method you are referring too?

Please let me know if I can give any further information to allow you to help. I am pretty stuck still on the best way to go about this project design.