Sas doesn't store your informat or your format to a macro variable.. So when you call &min_dttm you should notice a very long number and not any kind of date format. the Dt operator is when you have a date string, something like "10Mar2009:00:00:00.000"dt. simply remove the quote and the dt, and your process should work. aka have proc sql; delete * from TEST where processed_dttm=&min_dttm ... View more

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! ... View more

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

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

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

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! ... View more

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

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

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; ? ... View more

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