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;
... View more
