Need help using macro variables to loop through a program

Posts: 1

Need help using macro variables to loop through a program

I am looking for help regarding the use of macro variables.

I am a relatively inexperienced SAS programmer - having learned most of what I know by trial and error and modifying other peoples code!! I have a program that contains the following elements:

  1. Imports three different excel spreadsheets (1.xlsx, 2.xlsx, 3.xlsx) and creates 3 individual data sets – dataset1, dataset2 and dataset3
  2. Uses PROC SQL to join two of the datasets  (1&2) using date ranges.
  3. Uses PROC MEANS to calculate aggregated values
  4. Uses MERGE to combine the aggregated dataset (step 3) to the third dataset (3)
  5. Performs a series of PROC MEANS and MERGE operations on the combined dataset (step 4) to achieve the desired result.

Currently, I use %LET statements to substitute for text in filenames, variable names, value names and output filenames, so that I only have to change them in three places (1 for each input file) instead of throughout the program.

However I have a number of combinations of these three files that I have to run –

  1. 1.xlsx has three possible %LET….. 2011, 2012 and 2013
  2. 2.xlsx has six possible %LET………. cNeed help ommunity1, community2, community3, etc.
  3. 3.xlsx has 34 possible %LET……… chemical1, chemical2, chemical3……………

I could run the program 3x6x34 times (612  !!), but I would like to set it up so that it defines the values for the %Let statements, something like the following…

2011, community1, chemical1, chemical2, chemical3….

2011, community2, chemical1, chemical2, chemical3….

2011, community3, chemical1, chemical2, chemical3….     ….and so on.

thereby running the program for each combination.

I have searched the SAS forum and other resources for something similar, but have yet to find a solution.

Any help would be much appreciated.

Super User
Posts: 10,490

Re: Need help using macro variables to loop through a program

The general approach for automating tasks with the macro language is to have a working program for one case. Do you have that?

If so post the code and we can make suggestions for improvement.

Without seeing how the results of your let statements are used there is a bit of a hurdle to get over. It may well be if the variables are named as you are showing them that variable lists may be more helpful.

Also your statement about a "series of means and merges" makes one suspect that there may be ways to reduce the number of steps involved.

Ask a Question
Discussion stats
  • 1 reply
  • 2 in conversation