thank you so much. This one takes into the account the ending name may not be in ('CITY', 'TOWN', 'BOROUGH', 'VILLAGE', 'CDP').  Really appreciate your input.  ... View more

Your code works perfectly! Thank you so much!  ... View more

thank you Paige... I tried scan function it worked! ... View more

can i choose two solutions? this works great as well. ( I changed select distinct memname into 'conso.'||left(memname) to customize to my libary) ... View more

Thank you so much Suryakiran~ It works great~ ... View more

Thank you ballardw! it works perfectly! ... View more

oh my God! it works!!! it's so hilarious.. 🙂 thanks! ... View more

Thank you so much, Tom. After your reply, I understand it didn't work previously because the leading 0 is omitted. Now it works (code below). Do you know is there any way to create an array to store values of 0331 0630 0930 1231 and reference each one in a do loop, rather than repeat the same coding for all four cases? Something like    %Array(mmdd, 331, 630, 930, 1231) %do j = mmdd{1} %to mmdd{4}   I searched but no luck...thank you in advance....       options mlogic; options mprint on; options symbolgen; /* create a macro to combine reports from every quarter and every year into one file*/ %macro year(starty=1993, endy=2017, increy= 1, startm=0331, endm=1231, increm = 300);q %local i; %local j; %do i = &starty %to &endy %by &increy; %let j=0331; %let mmdd = %sysfunc(putn(&j,Z4)); %put &=j &=mmdd; proc import out = work.agloan&&i.&j datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv" dbms = csv replace; getnames = yes; datarow = 2; run; %end; %do i = &starty %to &endy %by &increy; %let j=0630; %let mmdd = %sysfunc(putn(&j,Z4)); %put &=j &=mmdd; proc import out = work.agloan&&i.&j datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv" dbms = csv replace; getnames = yes; datarow = 2; run; %end; %do i = &starty %to &endy %by &increy; %let j=0930; %let mmdd = %sysfunc(putn(&j,Z4)); %put &=j &=mmdd; proc import out = work.agloan&&i.&j datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv" dbms = csv replace; getnames = yes; datarow = 2; run; %end; %do i = &starty %to &endy %by &increy; %let j=1231; %let mmdd = %sysfunc(putn(&j,Z4)); %put &=j &=mmdd; proc import out = work.agloan&&i.&j datafile = "C:\Users\Chang Xu\Desktop\FIE Chang\Box Sync\FIE Chang\2nd paper\0-data\FDIC\raw_about other attribute\All_Reports_&&i&&mmdd.\All_Reports_&&i&&mmdd._Small Business Loans.csv" dbms = csv replace; getnames = yes; datarow = 2; run; %end; %mend; %year(starty=1993,endy=2017, increy=1, startm=0331, endm=1231, increm = 300)     ... View more

thank you, Tom. Learned a lot from your reply! ... View more