SAS loop containing many proc sql statements

Question

I am working in SAS EG and have a program that pulls from multiple databases, does some analysis, and outputs a summary table with some new data. I am now told that I need to do this dozens of times, and am very much struggling with making a loop do this in SAS with the internal proc SQL statements.

Let me try to summarize that again with a bit more detail.

Current code:

takes in employee number, fleet, position and outputs a summary table about various things they did that month compared to other people in their category (mostly using proc SQL).

I need to do:

Given another table with a list of dozens of those same variables (employee number, fleet, position) I need to generate the same summary information for each of those unique employee information.

Any help is greatly appreciated! Still very new to SAS (not SQL as much) so the more descriptive the better.

Are you asking how to use GROUP BY or normal SAS BY processing to preform all of the sub analyses in one run? Or are you looking for code generation techniques? Like SAS macro code. — Tom
@tom I would prefer to be able to do it in one run but have no idea how .. if its easier to do it in two programs I can do that but have never done that in SAS/SQL so not exactly sure how either — Michael Ancel
If you have a working program here's a tutorial that may help you out: github.com/statgeek/SAS-Tutorials/blob/master/… — Reeza

Tom Tom · Accepted Answer · 2021-06-01T15:50:32

In general in SAS you would define a macro to be able to rerun a multiple step process with different variables (or any other part of the code that needs to change). This is true whether the code you want to generate include PROC SQL steps or DATA steps or any other procedures.

Replace parts of the code that vary with references to macro variables. Wrap the code in a macro definition using those macro variables as the parameters. Then call the macro for each set of parameter values.

For example if you just wanted the input to be a single variable name your macro definition might look like:

%macro mymacro(var);
   ... use &VAR. in your analysis code to replace the hard coded variable name ...
%mend ;

Then you can call it multiple times.

%mymacro(var1);
%mymacro(var2);

SAS loop containing many proc sql statements

1 Answers