I have table of dates that I would like to loop thru and run a macro for each row that will create a properly name dataset from a rather large table. How can I do this in SAS?
Below is as far as I could get. Do I need to use an array instead and then use a Do..Loop?
There are several different approaches to do what you want. Both involve a SAS Macro program. The macro program described here (in a previous forum posting) is probably the more straightforward approach than the CALL EXECUTE approach. In either case, you would need to have a working macro program and a study of this program might give you some ideas of how to approach your particular problem: http://support.sas.com/forums/thread.jspa?messageID=10762⨊
The difference would be that instead of looking for the unique values of the SEX variable from SASHELP.CLASS, you would look for the unique values of the MYDATE variable from your dataset. Then, instead of an ODS CSV step, you would have the DATA step program that would create a properly named dataset for every unique DATE.
Your initial thoughts about approach are almost exactly what this forum posting illustrates. Instead of an array (which is a data step construction) -- the program uses a list of numbered macro variables and instead of a data step DO loop, the program uses a macro program %DO loop.
A possible alternative to using a macro to support the looping over the dates, uses data step looping and invokes with call execute(). This approach may look more complex. However it offers an alternative to "the only need for new SAS macros" if that is the case.
Imagine you need to run a big job by invoking some code like:[pre] %bigJob(as_at=31Dec2008) [/pre]The following will generate that code for each observation in data set "your_table_of_dates".
[pre]data _null_ ; * no output data set seems relevant here;
set your_table_of_dates(keep= run_date ) ;* assumes run_date holds your date parameter ;
call execute( '%nrstr( %%bigJob( as_at= ) ' ) ;
call execute( put( run_date, date9. )) ; *call execute has to submit the date as text ;
call execute( " ) " ); * closing the invocation of bigJob( ;
That %nrstr( % is needed to delay resolving macro %bigJob until after this data _null_ set has run