Looping through table names within a macro

[GRC_coder]

Hello,

It's been a while since I've written macros so I'll need a refresher course on my task. I am using a prewritten macro and need help implementing a loop (or an easier solution) to run the macro for 100+ tables. I have stored the table names I want to use within a SQL table that I call directly into SAS with the variables "INPUT_TABLE" for CLAIMS and the variable "FINAL_TABLE" for FINAL_TABLE (which outputs the analyses being done in the macro).

So how can I run through each table without having to change the table names each run?

Code:

/* Example Table Dataset */
data SAS_FOLDER.ANALYSIS_TABLES;
  input INPUT_TABLE FINAL_TABLE;
  datalines;
  SQL_DATABASE.OVER65_2019 SAS_FOLDER.OVER65_2019_final
  SQL_DATABASE.OVER65_2020 SAS_FOLDER.OVER65_2020_final
  SQL_DATABASE.OVER65_2021 SAS_FOLDER.OVER65_2021_final
  SQL_DATABASE.OVER65_2022 SAS_FOLDER.OVER65_2022_final
  SQL_DATABASE.LOW_HTN_2019 SAS_FOLDER.LOW_HTN_2019_final
  SQL_DATABASE.LOW_HTN_2020 SAS_FOLDER.LOW_HTN_2020_final
;
run;

/* MACRO starts */
%MACRO COC;

%LET PATIENTID= patid;                      * !!! <---- SET NAME OF PATIENT IDENTIFIER;
%LET CLAIMS= DATABASE.TABLE; 
%LET FINAL_TABLE = SAS_FOLDER.TABLE_FINAL;
%LET PROV_NUM=providerid;                  * !!! <---- SET NAME UNIQUE PROVIDER ID NUMBER;
%LET SDATE= admit_date;                       * !!! <---- SET NAME OF DATE OF SERVICE ON THE PROFESSIONAL CLAIM;

/*
/******************************************************/
/**CODES TO BE USED**/
/******************************************************/
/*


/**DEFINE COHORT OF PATIENTS**/

proc sql;
create table PROFESSIONAL as
select *
from &CLAIMS;
quit;

data PROFESSIONAL; set PROFESSIONAL;

/**KEEP ONLY VALID PROVIDERS**/
IF &PROV_NUM NE ' ';

/**CREATE THIS VARIABLE TO COUNT UP NUMBER OF CLAIMS**/
NCLAIMS= 1;

KEEP &PATIENTID &SDATE &PROV_NUM NCLAIMS;
		
		
/**KEEP ONE CLAIM PER DAY PER PHYSICIAN*/
PROC SORT DATA= PROFESSIONAL NODUPKEY OUT= NODUP; BY &PATIENTID &SDATE &PROV_NUM; run;
PROC PRINT DATA= NODUP (OBS=10); run;

/**CALCULATE THE NUMBER OF VISITS PER PROVIDER**/
PROC SUMMARY DATA= NODUP NWAY;
		CLASS &PATIENTID &PROV_NUM;
		VAR NCLAIMS;
		OUTPUT OUT= PATIENT_PROV_DYAD (DROP= _:) SUM= ;
RUN;
PROC PRINT DATA= PATIENT_PROV_DYAD (OBS=10); run;

/**CALCULATE THE TOTAL NUMBER OF VISITS PER PATIENT**/
PROC SUMMARY DATA= NODUP NWAY;
		CLASS &PATIENTID;
		VAR NCLAIMS;
		OUTPUT OUT= PATIENT (DROP= _:) SUM(NCLAIMS)= TOTAL_VISITS;
RUN;
PROC PRINT DATA= PATIENT (OBS=10); run;

proc univariate data = PATIENT;
var TOTAL_VISITS;
run;

/**MERGE NUMBER OF VISITS BY PROVIDER TO THE TOTAL NUMBER OF VISITS*/
PROC SORT DATA= PATIENT_PROV_DYAD; BY &PATIENTID; run;
PROC SORT DATA= PATIENT; BY &PATIENTID; run;

DATA UPC;
		MERGE PATIENT_PROV_DYAD PATIENT;
		BY &PATIENTID;
		UPC= NCLAIMS/TOTAL_VISITS;
RUN;

PROC SORT DATA= UPC; BY &PATIENTID; run;

/**BICE-BOXERMAN- COC INDEX**/
DATA BB_COC (KEEP= &PATIENTID X TOTAL_VISITS);
		SET UPC;
		RETAIN X;
		BY &PATIENTID;
		IF FIRST.&PATIENTID THEN X= .;
		X+ (NCLAIMS*NCLAIMS);
		IF LAST.&PATIENTID THEN OUTPUT;
RUN;

DATA &FINAL_TABLE;
		SET BB_COC;
		BB_COC= (X-TOTAL_VISITS)/(TOTAL_VISITS*(TOTAL_VISITS-1));
		
		if TOTAL_VISITS ge 2;
RUN;

PROC MEANS DATA= &FINAL_TABLE;
		VAR BB_COC;
RUN;

proc univariate data = &FINAL_TABLE;
		var BB_COC;
run;

%MEND COC;

%COC;

[Kurt_Bremser · Accepted solution]

Change the header of the macro to make the dataset names parameters:

%MACRO COC(claims,final_table);

%LET PATIENTID= patid;                      * !!! <---- SET NAME OF PATIENT IDENTIFIER;
%LET PROV_NUM=providerid;                  * !!! <---- SET NAME UNIQUE PROVIDER ID NUMBER;
%LET SDATE= admit_date;                       * !!! <---- SET NAME OF DATE OF SERVICE ON THE PROFESSIONAL CLAIM;

Then you can call it from a DATA step:

data _null_;
set sas_folder.analysis_tables;
call execute(cats('%nrstr(%coc(',input_table,',',final_table,'))'));
run;

[GRC_coder]

This worked like magic! Thanks a bunch Kurt.

[Patrick]

Below sample code to illustrate how to call a macro with data driven parameter passing.

/* Example Table Dataset */
data work.ANALYSIS_TABLES;
  input INPUT_TABLE:$60. FINAL_TABLE:$60.;
  datalines;
  SQL_DATABASE.OVER65_2019 SAS_FOLDER.OVER65_2019_final
  SQL_DATABASE.OVER65_2020 SAS_FOLDER.OVER65_2020_final
  SQL_DATABASE.OVER65_2021 SAS_FOLDER.OVER65_2021_final
  SQL_DATABASE.OVER65_2022 SAS_FOLDER.OVER65_2022_final
  SQL_DATABASE.LOW_HTN_2019 SAS_FOLDER.LOW_HTN_2019_final
  SQL_DATABASE.LOW_HTN_2020 SAS_FOLDER.LOW_HTN_2020_final
;
run;

%MACRO COC(
  CLAIMS
  ,FINAL_TABLE
  );

  data demo;
    CLAIMS       = "&CLAIMS";
    FINAL_TABLE  = "&FINAL_TABLE";
  run;
  proc print data=demo;
  run;

%mend;

data _null_;
  set work.ANALYSIS_TABLES;
  cmd=cats( '%coc(',INPUT_TABLE,',',FINAL_TABLE,');' );
  call execute(cmd);
run;

You could of course also consider to first combine all your 100 source tables (only the variables you need) and then run the macro only once. 

It's certainly also worth that you go through the actual SAS code within the macro and streamline it a bit - like the first Proc SQL looks totally unnecessary.