BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
GRC_coder
Calcite | Level 5

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

 

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Calcite | Level 5

This worked like magic! Thanks a bunch Kurt.

Patrick
Opal | Level 21

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. 

 

 

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 320 views
  • 0 likes
  • 3 in conversation