https://communities.sas.com/t5/New-SAS-User/Turn-these-datasteps-into-a-macro-or-other-repeated-process/m-p/685219#M24279 <P>As the excel type is xlsx I proffered the datastep then the proc import.</P> <P>Please check next code:</P> <PRE><CODE class=" language-sas">%let year = 2017; libname excelin xlsx "C:\PCC_Trends\PCC_&amp;year..xlsx"; %macro exec(suffix); data work.PCC_&amp;year.&amp;suffix; set excelin.Section&amp;suffix; run; proc sort data=work.PCC_&amp;year.&amp;suffix; by OSHPD_ID; run; %mend exec; %exec(a); %exec(b); %exec(c); %exec(d); %exec(e); %exec(f); data PCC_&amp;year; merge PCC_&amp;year.a PCC_&amp;year.b PCC_&amp;year.c ; by OSHPD_ID; run; data PCC_&amp;year.nonresp; merge PCC_&amp;year.d PCC_&amp;year.e PCC_&amp;year.f ; by OSHPD_ID; run;</CODE></PRE> <P>&nbsp;</P> Sat, 19 Sep 2020 20:24:12 GMT Shmuel 2020-09-19T20:24:12Z https://communities.sas.com/t5/New-SAS-User/Turn-these-datasteps-into-a-macro-or-other-repeated-process/m-p/685211#M24278 <P>I need to make this more efficient. I have 10 datasets that I want to do this same process to (e.g. 2008:2018).</P><P>&nbsp;</P><P>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!</P><P>&nbsp;</P><P>&nbsp;</P><P>/***2017***/</P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>PROC</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">IMPORT</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">OUT</FONT><FONT face="Courier New" size="2">= WORK.PCC_2017a</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DATAFILE</FONT><FONT face="Courier New" size="2">= </FONT><FONT face="Courier New" size="2" color="#800080">"C:\PCC_Trends\PCC_2017.xlsx"</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DBMS</FONT><FONT face="Courier New" size="2">=XLSX REPLACE;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">Sheet</FONT><FONT face="Courier New" size="2">=Sectiona;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">GETNAMES</FONT><FONT face="Courier New" size="2">=YES;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>RUN</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>PROC</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">IMPORT</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">OUT</FONT><FONT face="Courier New" size="2">= WORK.PCC_2017b</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DATAFILE</FONT><FONT face="Courier New" size="2">= </FONT><FONT face="Courier New" size="2" color="#800080">"C:\PCC_Trends\PCC_2017.xlsx"</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DBMS</FONT><FONT face="Courier New" size="2">=XLSX REPLACE;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">Sheet</FONT><FONT face="Courier New" size="2">=Sectionb;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">GETNAMES</FONT><FONT face="Courier New" size="2">=YES;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>RUN</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>PROC</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">IMPORT</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">OUT</FONT><FONT face="Courier New" size="2">= WORK.PCC_2017c</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DATAFILE</FONT><FONT face="Courier New" size="2">= </FONT><FONT face="Courier New" size="2" color="#800080">"C:\PCC_Trends\PCC_2017.xlsx"</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DBMS</FONT><FONT face="Courier New" size="2">=XLSX REPLACE;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">Sheet</FONT><FONT face="Courier New" size="2">=Sectionc;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">GETNAMES</FONT><FONT face="Courier New" size="2">=YES;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>RUN</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>PROC</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">IMPORT</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">OUT</FONT><FONT face="Courier New" size="2">= WORK.PCC_2017d</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DATAFILE</FONT><FONT face="Courier New" size="2">= </FONT><FONT face="Courier New" size="2" color="#800080">"C:\PCC_Trends\PCC_2017.xlsx"</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DBMS</FONT><FONT face="Courier New" size="2">=XLSX REPLACE;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">Sheet</FONT><FONT face="Courier New" size="2">=NonRespa;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">GETNAMES</FONT><FONT face="Courier New" size="2">=YES;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>RUN</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>PROC</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">IMPORT</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">OUT</FONT><FONT face="Courier New" size="2">= WORK.PCC_2017e</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DATAFILE</FONT><FONT face="Courier New" size="2">= </FONT><FONT face="Courier New" size="2" color="#800080">"C:\PCC_Trends\PCC_2017.xlsx"</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DBMS</FONT><FONT face="Courier New" size="2">=XLSX REPLACE;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">Sheet</FONT><FONT face="Courier New" size="2">=NonRespb;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">GETNAMES</FONT><FONT face="Courier New" size="2">=YES;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>RUN</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>PROC</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">IMPORT</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">OUT</FONT><FONT face="Courier New" size="2">= WORK.PCC_2017f</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DATAFILE</FONT><FONT face="Courier New" size="2">= </FONT><FONT face="Courier New" size="2" color="#800080">"C:\PCC_Trends\PCC_2017.xlsx"</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">DBMS</FONT><FONT face="Courier New" size="2">=XLSX REPLACE;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">Sheet</FONT><FONT face="Courier New" size="2">=NonRespc;</FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">GETNAMES</FONT><FONT face="Courier New" size="2">=YES;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>RUN</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>proc</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">sort</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">data</FONT><FONT face="Courier New" size="2">=PCC_2017a; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>proc</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">sort</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">data</FONT><FONT face="Courier New" size="2">=PCC_2017b; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>proc</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">sort</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">data</FONT><FONT face="Courier New" size="2">=PCC_2017c; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>proc</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">sort</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">data</FONT><FONT face="Courier New" size="2">=PCC_2017d; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>proc</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">sort</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">data</FONT><FONT face="Courier New" size="2">=PCC_2017e; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>proc</STRONG></FONT> <STRONG><FONT face="Courier New" size="2" color="#000080">sort</FONT></STRONG> <FONT face="Courier New" size="2" color="#0000ff">data</FONT><FONT face="Courier New" size="2">=PCC_2017f; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>data</STRONG></FONT><FONT face="Courier New" size="2"> PCC_2017; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">merge</FONT><FONT face="Courier New" size="2"> PCC_2017a PCC_2017b PCC_2017c ; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>data</STRONG></FONT><FONT face="Courier New" size="2"> PCC_2017nonresp; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">merge</FONT><FONT face="Courier New" size="2"> PCC_2017d PCC_2017e PCC_2017f ; </FONT></P><P><FONT face="Courier New" size="2" color="#0000ff">by</FONT><FONT face="Courier New" size="2"> OSHPD_ID; </FONT></P><P><FONT face="Courier New" size="2" color="#000080"><STRONG>run</STRONG></FONT><FONT face="Courier New" size="2">;</FONT></P><P>&nbsp;</P> Sat, 19 Sep 2020 18:42:01 GMT https://communities.sas.com/t5/New-SAS-User/Turn-these-datasteps-into-a-macro-or-other-repeated-process/m-p/685211#M24278 erikhendrickson 2020-09-19T18:42:01Z https://communities.sas.com/t5/New-SAS-User/Turn-these-datasteps-into-a-macro-or-other-repeated-process/m-p/685219#M24279 <P>As the excel type is xlsx I proffered the datastep then the proc import.</P> <P>Please check next code:</P> <PRE><CODE class=" language-sas">%let year = 2017; libname excelin xlsx "C:\PCC_Trends\PCC_&amp;year..xlsx"; %macro exec(suffix); data work.PCC_&amp;year.&amp;suffix; set excelin.Section&amp;suffix; run; proc sort data=work.PCC_&amp;year.&amp;suffix; by OSHPD_ID; run; %mend exec; %exec(a); %exec(b); %exec(c); %exec(d); %exec(e); %exec(f); data PCC_&amp;year; merge PCC_&amp;year.a PCC_&amp;year.b PCC_&amp;year.c ; by OSHPD_ID; run; data PCC_&amp;year.nonresp; merge PCC_&amp;year.d PCC_&amp;year.e PCC_&amp;year.f ; by OSHPD_ID; run;</CODE></PRE> <P>&nbsp;</P> Sat, 19 Sep 2020 20:24:12 GMT https://communities.sas.com/t5/New-SAS-User/Turn-these-datasteps-into-a-macro-or-other-repeated-process/m-p/685219#M24279 Shmuel 2020-09-19T20:24:12Z