Control table and macros

[forumsguy]

Hello, My requirement is as follows:

will be given a number of SAS tables with account level records. 

so we can join the tables based on account_id each table will contain certain attributes of an account for say 10 years 

but there will be one record per account. so outs_bal for an account will be in outs_bal_1 .... outs_bal_n columns

Need to create a master control table containing, for example, portfolio name, macro name, location of the macro source code, and things like that using that control table, your program will read in the macro definition and run the code against those "array" datasets and append to a big monthly dataset that monthly dataset will contain account level records from the array tables pluss addtional columns created by macros already present.

Anyone worked on similar things. I think of using %include in my program. Ultimately, this has to be deployed on SAS DI. So any other approach ?? Any help is appreciated

[SASKiwi]

I don't use SAS DI but do extensively use SAS macros. I strongly recommend you look at AUTOCALL macros. If you haven't used these before they are just a bunch of SAS programs, each of which only contains one macro definition matched by the name of the program file. For example a macro called MyMacro would be stored in a file called MyMacro.sas.

You put all of these macro programs in a single folder and this is called an AUTOCALL macro library. Let's say the folder is called C:\MySASMacroLibrary. Then in your SAS code you write:

options sasautos = ("C:\MySASMacroLibrary", SASAUTOS); * SASAUTOS allows you to reference SAS-supplied macros.

And to call your macro: %MyMacro;

The beauty of this approach is that NO %include is required, and the macro is only compiled the first time it is referenced in your code. Also a macro is only compiled if it is called in your code. You can also use a file reference instead of a folder reference if you prefer.

[LinusH]

Your first requirement seems clear, looks like you need to to some joining and transposing.

But for the macro part, it's hard from the the information you're providing to see the actual solution.

Generally, macro is a tool for the Base programmer to make some clever coding, and ultimately make the application more maintenance friendly.

The same problem is solved differently in DI Studio, where the most important building blocks are the standard transformations. using macro code here could lead to that functionality is hidden from the developer, hence will make maintenance harder.

So, try to solve your problem by using standard transformations. If not feasible, try to use User Written Transformations, where at least you macro code could be visible and available for DI Studio maintenance personnel.