topic Re: Calling macro inside of macros, string limit length in SAS Programming

Calling macro inside of macros, string limit length

Anotherdream — Fri, 02 May 2014 18:07:07 GMT

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;

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;

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!

Re: Calling macro inside of macros, string limit length

Tom — Fri, 02 May 2014 18:35:37 GMT

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 ;

Re: Calling macro inside of macros, string limit length

Anotherdream — Fri, 02 May 2014 19:07:03 GMT

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.

Re: Calling macro inside of macros, string limit length

Tom — Fri, 02 May 2014 20:10:15 GMT

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;

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;

Re: Calling macro inside of macros, string limit length

Anotherdream — Fri, 02 May 2014 20:24:20 GMT

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;

Re: Calling macro inside of macros, string limit length

Tom — Fri, 02 May 2014 21:22:09 GMT

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.

Re: Calling macro inside of macros, string limit length

Anotherdream — Fri, 02 May 2014 21:49:47 GMT

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

Re: Calling macro inside of macros, string limit length

Tom — Fri, 02 May 2014 22:32:25 GMT

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 ?

Re: Calling macro inside of macros, string limit length

Reeza — Fri, 02 May 2014 23:33:03 GMT

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.

Re: Calling macro inside of macros, string limit length

Anotherdream — Mon, 05 May 2014 13:55:59 GMT

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;

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

Re: Calling macro inside of macros, string limit length

Reeza — Mon, 05 May 2014 15:14:36 GMT

Begin by explaining and detailing your problem thoroughly

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

Re: Calling macro inside of macros, string limit length

Anotherdream — Tue, 06 May 2014 14:25:27 GMT

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!

Re: Calling macro inside of macros, string limit length

RW9 — Tue, 06 May 2014 14:40:54 GMT

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):

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.

Re: Calling macro inside of macros, string limit length

Anotherdream — Tue, 06 May 2014 15:19:50 GMT

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

Re: Calling macro inside of macros, string limit length

RW9 — Tue, 06 May 2014 15:21:56 GMT

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

Re: Calling macro inside of macros, string limit length

RichardinOz — Tue, 06 May 2014 23:07:46 GMT

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.

Re: Calling macro inside of macros, string limit length

Anotherdream — Thu, 15 May 2014 16:15:40 GMT

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....

Re: Calling macro inside of macros, string limit length

Anotherdream — Thu, 15 May 2014 16:19:38 GMT

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.

Re: Calling macro inside of macros, string limit length

Anotherdream — Mon, 19 May 2014 16:21:30 GMT

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!

Re: Calling macro inside of macros, string limit length

Reeza — Mon, 19 May 2014 22:57:40 GMT

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