how to write a macro to pull the title, foootnote of tables from a dataset, identified by a table number.
Assume a sas dataset is available which has the table no, appropriate title(s) and footnote(s) as observations.
The title and footnote got thereby will be used to populate the concerned table appropriately.Some tables have multiple titles and/or footnotes
SAS titles and footnotes are stored in the program code, not in tables.
If YOU store titles and footnotes in a data set (table) then YOU know the name of the field/variable/column/attribute that is storing that information and YOU can simply write your own code to access it.
I'm having a hard time visualizing how this dataset works...it has the data for analysis AND has the titles and footnotes that should be used for each "table"? Or, this is some kind of "organizing" dataset and if somebody produces table 1, then their job is to look up in the title dataset what the approved title is for table 1?
There are many ways to use the SAS Macro facility and this could be one of them (where something in the data drives something in the code -- in this case a title statement).
I generally recommend a quite conservative approach to using SAS macro variables and SAS macro programs -- this method has never failed me.
1) Start with a working SAS program. That means -- have a working program that doesn't use macro variables at all. In your case, it may mean creating a temp dataset that contains just the titles and footnotes for one table and being able to do a PROC PRINT on that temp table so you can see that you only have your titles and footnotes of interest. This is going to be a new program.
2) Slowly, convert your existing SAS program -- that one that produces TABLE 1 -- to use macro variables with a %LET statement -- make sure you have the syntax of USING the macro variable with the title correctly. Even if it's something as seemingly obvious as this:
%let tstr1 = This is my Title;
%let tstr2 = This is title 2;
In this way, you'll learn what the syntactically correct title statement looks like -- for example, if you first coded your title statement with single quotes, you would have found that single quotes did not allow the macro variables to resolve correctly.
Now, your issue becomes how to take the information in the dataset from step 1 and create the macro variable &TSTR to be used in the TITLE statement (I'd go easy and get one TITLE statement before I jumped into dealing with multiple statements.) So, now you will need to study the SAS Macro documentation for the possible ways to create macro variables from data -- either CALL SYMPUT or SELECT...INTO will prove better methods than %LET. So you revise your program from #1, perhaps adding the code from #2 and you get a new program that not only reads the appropriate title and/or footnote statements, but uses them -- but at this point, only uses the first title statement in an automated fashion instead of using ALL the title statements..
3) Next you have to decide whether you need any macro constructs -- like %IF or %DO -- since you can have up to 10 titles and 10 footnote statements, I suspect you will have to go down this road, too. If you need macro-specific language constructs like %IF or %DO, then you will have to learn about creating a macro program which will essentially issue your title and footnote statements for you.
The thing about the SAS Macro Facility that a lot of folks overlook is that the Macro facility is just doing your typing for you. There's no magic. I can use the Macro facility to build and type one statement, part of one statement, a lot of statements, a whole program, a lot of whole programs -- any code that's generated by the Macro facility still goes to be compiled and executed. That's why you need to have a working SAS program to start with -- you have to know that your program WORKS first and where macro variables and macro logic can be used to make your life easier before you undertake using the Macro facility.
4) Put your working code into a SAS macro program. Decide on your parameters (I prefer keyword parameters because they let you set defaults for the macro variable value and they don't have to be specified in a set order), decide on how you're going to implement macro logic or how you're going to use a %DO loop to generate multiple table statements. You may spend quite a bit of time in this phase refining your program code, as you begin to understand what the Macro facility can do for you.
Finally, decide how you're going to call or invoke your macro program(are you going to compile the macro program in every session right before you use it; are you going to store the macro program in an autocall library; are you going to store the compiled macro program?) -- I recommend using session-compiled macro programs for beginners.