Turn these datasteps into a macro or other repeated process.

[erikhendrickson]

I need to make this more efficient. I have 10 datasets that I want to do this same process to (e.g. 2008:2018).

How can I make this a repeated process or macro so that the date variables are able to change. I have the 2017 example below...THANK YOU!

/***2017***/

PROC IMPORT OUT= WORK.PCC_2017a

DATAFILE= "C:\PCC_Trends\PCC_2017.xlsx"

DBMS=XLSX REPLACE;

Sheet=Sectiona;

GETNAMES=YES;

RUN;

PROC IMPORT OUT= WORK.PCC_2017b

DATAFILE= "C:\PCC_Trends\PCC_2017.xlsx"

DBMS=XLSX REPLACE;

Sheet=Sectionb;

GETNAMES=YES;

RUN;

PROC IMPORT OUT= WORK.PCC_2017c

DATAFILE= "C:\PCC_Trends\PCC_2017.xlsx"

DBMS=XLSX REPLACE;

Sheet=Sectionc;

GETNAMES=YES;

RUN;

PROC IMPORT OUT= WORK.PCC_2017d

DATAFILE= "C:\PCC_Trends\PCC_2017.xlsx"

DBMS=XLSX REPLACE;

Sheet=NonRespa;

GETNAMES=YES;

RUN;

PROC IMPORT OUT= WORK.PCC_2017e

DATAFILE= "C:\PCC_Trends\PCC_2017.xlsx"

DBMS=XLSX REPLACE;

Sheet=NonRespb;

GETNAMES=YES;

RUN;

PROC IMPORT OUT= WORK.PCC_2017f

DATAFILE= "C:\PCC_Trends\PCC_2017.xlsx"

DBMS=XLSX REPLACE;

Sheet=NonRespc;

GETNAMES=YES;

RUN;

proc sort data=PCC_2017a;

by OSHPD_ID;

run;

proc sort data=PCC_2017b;

by OSHPD_ID;

run;

proc sort data=PCC_2017c;

by OSHPD_ID;

run;

proc sort data=PCC_2017d;

by OSHPD_ID;

run;

proc sort data=PCC_2017e;

by OSHPD_ID;

run;

proc sort data=PCC_2017f;

by OSHPD_ID;

run;

data PCC_2017;

merge PCC_2017a PCC_2017b PCC_2017c ;

by OSHPD_ID;

run;

data PCC_2017nonresp;

merge PCC_2017d PCC_2017e PCC_2017f ;

by OSHPD_ID;

run;

[Shmuel]

As the excel type is xlsx I proffered the datastep then the proc import.

Please check next code:

%let year = 2017;
libname excelin xlsx "C:\PCC_Trends\PCC_&year..xlsx";

%macro exec(suffix);
  data work.PCC_&year.&suffix;
   set excelin.Section&suffix;
  run;
  
  proc sort data=work.PCC_&year.&suffix;
    by OSHPD_ID;
  run;
%mend exec;
%exec(a);
%exec(b);
%exec(c);
%exec(d);
%exec(e);
%exec(f);

data PCC_&year;
merge PCC_&year.a PCC_&year.b PCC_&year.c ;
  by OSHPD_ID;
run;

data PCC_&year.nonresp;
merge PCC_&year.d PCC_&year.e PCC_&year.f ;
  by OSHPD_ID;
run;