How to create SAS macro loop with multiple SQL procedures inside

Data and Databases
1

I’m using SAS Enterprise Guide and built a program that connects to several databases, performs calculations, and creates summary reports with processed information. Now my manager wants me to run this same process for many different records, but I can’t figure out how to wrap all my proc SQL code inside a loop.

My current setup:
I input worker ID, department, and job title, then generate monthly performance comparisons using mostly SQL procedures.

What I need to accomplish:
I have a master table containing multiple rows of worker IDs, departments, and job titles. I want to automatically process each row and create the same summary analysis for every unique combination.

/* Example of what I'm trying to loop through */
proc sql;
  select worker_id, dept_code, role_type 
  from master_roster;
quit;

/* This analysis needs to run for each record */
proc sql;
  create table monthly_stats as
  select avg(performance_score) as avg_perf
  from employee_data 
  where worker_id = &current_worker;
quit;

I’m still learning SAS macros and loops. Any detailed examples would be really helpful since I’m more comfortable with SQL than SAS programming.

2

for macro loops with sql, i use %macro and %do. something like %macro process_workers; %do i=1 %to &total_records; proc sql; select worker_id into :current_id from master_roster(firstobs=&i obs=&i); /* your analysis code here */ %end; %mend; works well. just get the record count first with another sql step.

3

hey liam! a macro with call execute could be a neat way to do this. i’ve been in a similar spot using datasets instead of sql tho. are there any errors you’re running into, or just not sure how to kick things off? also, is your master_roster all unique entries?

4

Skip the traditional macro loops and use a data step with call execute instead. Read your master_roster once and let it generate the code on the fly. Set up your macro with parameters for worker_id, dept_code, and role_type. Then use a data step to read each row from master_roster and call execute to run your macro for each record. You’ll get better performance since you’re not making multiple passes through the data or wrestling with complex macro variables. Just put all your current proc SQL statements in the macro and parameterize them with the input values. This handles datasets of any size automatically - no need to count records first, so it’s way more reliable than fixed loops.