Folks,
Is there anyway to simplify this type of process. Basically I want to carry out the same steps for 98 different datasets.
Read in from one library and write into another (maybe transform drop and add some variables too).
Any help welcome.
******Where you want to read your data in from*********;
libname x0 "\\Inputlibrary\2010M01\";
libname x1 "\\Inputlibrary\2010M02\";
libname x2 "\\Inputlibrary\2010M03\";
libname x3 "\\Inputlibrary\2010M04\";
libname x4 "\\Inputlibrary\2010M05\";
libname x5 "\\Inputlibrary\2010M06\";
libname x6 "\\Inputlibrary\2010M07\";
libname x7 "\\Inputlibrary\2010M08\";
libname x8 "\\Inputlibrary\2010M09\";
libname x9 "\\Inputlibrary\2010M10\";
libname x10 "\\Inputlibrary\2010M11\";
libname x11 "\\Inputlibrary\2010M12\";
libname x12 "\\Inputlibrary\2011M01\";
libname x13 "\\Inputlibrary\2011M02\";
libname x14 "\\Inputlibrary\2011M03\";
libname x15 "\\Inputlibrary\2011M04\";
libname x16 "\\Inputlibrary\2011M05\";
libname x17 "\\Inputlibrary\2011M06\";
libname x18 "\\Inputlibrary\2011M07\";
libname x19 "\\Inputlibrary\2011M08\";
libname x20 "\\Inputlibrary\2011M09\";
libname x21 "\\Inputlibrary\2011M10\";
libname x22 "\\Inputlibrary\2011M11\";
libname x23 "\\Inputlibrary\2011M12\";
libname x24 "\\Inputlibrary\2012M01\";
libname x25 "\\Inputlibrary\2012M02\";
libname x26 "\\Inputlibrary\2012M03\";
libname x27 "\\Inputlibrary\2012M04\";
libname x28 "\\Inputlibrary\2012M05\";
libname x29 "\\Inputlibrary\2012M06\";
libname x30 "\\Inputlibrary\2012M07\";
libname x31 "\\Inputlibrary\2012M08\";
libname x32 "\\Inputlibrary\2012M09\";
libname x33 "\\Inputlibrary\2012M10\";
libname x34 "\\Inputlibrary\2012M11\";
libname x35 "\\Inputlibrary\2012M12\";
libname x36 "\\Inputlibrary\2013M01\";
libname x37 "\\Inputlibrary\2013M02\";
libname x38 "\\Inputlibrary\2013M03\";
libname x39 "\\Inputlibrary\2013M04\";
libname x40 "\\Inputlibrary\2013M05\";
libname x41 "\\Inputlibrary\2013M06\";
libname x42 "\\Inputlibrary\2013M07\";
libname x43 "\\Inputlibrary\2013M08\";
libname x44 "\\Inputlibrary\2013M09\";
libname x45 "\\Inputlibrary\2013M10\";
libname x46 "\\Inputlibrary\2013M11\";
libname x47 "\\Inputlibrary\2013M12\";
libname x48 "\\Inputlibrary\2014M01\";
libname x49 "\\Inputlibrary\2014M02\";
libname x50 "\\Inputlibrary\2014M03\";
libname x51 "\\Inputlibrary\2014M04\";
libname x52 "\\Inputlibrary\2014M05\";
libname x53 "\\Inputlibrary\2014M06\";
libname x54 "\\Inputlibrary\2014M07\";
libname x55 "\\Inputlibrary\2014M08\";
libname x56 "\\Inputlibrary\2014M09\";
libname x57 "\\Inputlibrary\2014M10\";
libname x58 "\\Inputlibrary\2014M11\";
libname x59 "\\Inputlibrary\2014M12\";
libname x60 "\\Inputlibrary\2015M01\";
libname x61 "\\Inputlibrary\2015M02\";
libname x62 "\\Inputlibrary\2015M03\";
libname x63 "\\Inputlibrary\2015M04\";
libname x64 "\\Inputlibrary\2015M05\";
libname x65 "\\Inputlibrary\2015M06\";
libname x66 "\\Inputlibrary\2015M07\";
libname x67 "\\Inputlibrary\2015M08\";
libname x68 "\\Inputlibrary\2015M09\";
libname x69 "\\Inputlibrary\2015M10\";
libname x70 "\\Inputlibrary\2015M11\";
libname x71 "\\Inputlibrary\2015M12\";
libname x72 "\\Inputlibrary\2016M01\";
libname x73 "\\Inputlibrary\2016M02\";
libname x74 "\\Inputlibrary\2016M03\";
libname x75 "\\Inputlibrary\2016M04\";
libname x76 "\\Inputlibrary\2016M05\";
libname x77 "\\Inputlibrary\2016M06\";
libname x78 "\\Inputlibrary\2016M07\";
libname x79 "\\Inputlibrary\2016M08\";
libname x80 "\\Inputlibrary\2016M09\";
libname x81 "\\Inputlibrary\2016M10\";
libname x82 "\\Inputlibrary\2016M11\";
libname x83 "\\Inputlibrary\2016M12\";
libname x84 "\\Inputlibrary\2017M01\";
libname x85 "\\Inputlibrary\2017M02\";
libname x86 "\\Inputlibrary\2017M03\";
libname x87 "\\Inputlibrary\2017M04\";
libname x88 "\\Inputlibrary\2017M05\";
libname x89 "\\Inputlibrary\2017M06\";
libname x90 "\\Inputlibrary\2017M07\";
libname x91 "\\Inputlibrary\2017M08\";
libname x92 "\\Inputlibrary\2017M09\";
libname x93 "\\Inputlibrary\2017M10\";
libname x94 "\\Inputlibrary\2017M11\";
libname x95 "\\Inputlibrary\2017M12\";
libname x96 "\\Inputlibrary\2018M01\";
libname x97 "\\Inputlibrary\2018M02\";
libname x98 "\\Inputlibrary\2018M03\";
*******Library you want to output to;
libname x101 "\\Outputlibrary\2010M01\";
libname x102 "\\Outputlibrary\2010M02\";
libname x103 "\\Outputlibrary\2010M03\";
libname x104 "\\Outputlibrary\2010M04\";
libname x105 "\\Outputlibrary\2010M05\";
libname x106 "\\Outputlibrary\2010M06\";
libname x107 "\\Outputlibrary\2010M07\";
libname x108 "\\Outputlibrary\2010M08\";
libname x109 "\\Outputlibrary\2010M09\";
libname x110 "\\Outputlibrary\2010M10\";
libname x111 "\\Outputlibrary\2010M11\";
libname x112 "\\Outputlibrary\2010M12\";
libname x113 "\\Outputlibrary\2011M01\";
libname x114 "\\Outputlibrary\2011M02\";
libname x115 "\\Outputlibrary\2011M03\";
libname x116 "\\Outputlibrary\2011M04\";
libname x117 "\\Outputlibrary\2011M05\";
libname x118 "\\Outputlibrary\2011M06\";
libname x119 "\\Outputlibrary\2011M07\";
libname x120 "\\Outputlibrary\2011M08\";
libname x121 "\\Outputlibrary\2011M09\";
libname x122 "\\Outputlibrary\2011M10\";
libname x123 "\\Outputlibrary\2011M11\";
libname x124 "\\Outputlibrary\2011M12\";
libname x125 "\\Outputlibrary\2012M01\";
libname x126 "\\Outputlibrary\2012M02\";
libname x127 "\\Outputlibrary\2012M03\";
libname x128 "\\Outputlibrary\2012M04\";
libname x129 "\\Outputlibrary\2012M05\";
libname x130 "\\Outputlibrary\2012M06\";
libname x131 "\\Outputlibrary\2012M07\";
libname x132 "\\Outputlibrary\2012M08\";
libname x133 "\\Outputlibrary\2012M09\";
libname x134 "\\Outputlibrary\2012M10\";
libname x135 "\\Outputlibrary\2012M11\";
libname x136 "\\Outputlibrary\2012M12\";
libname x137 "\\Outputlibrary\2013M01\";
libname x138 "\\Outputlibrary\2013M02\";
libname x139 "\\Outputlibrary\2013M03\";
libname x140 "\\Outputlibrary\2013M04\";
libname x141 "\\Outputlibrary\2013M05\";
libname x142 "\\Outputlibrary\2013M06\";
libname x143 "\\Outputlibrary\2013M07\";
libname x144 "\\Outputlibrary\2013M08\";
libname x145 "\\Outputlibrary\2013M09\";
libname x146 "\\Outputlibrary\2013M10\";
libname x147 "\\Outputlibrary\2013M11\";
libname x148 "\\Outputlibrary\2013M12\";
libname x149 "\\Outputlibrary\2014M01\";
libname x150 "\\Outputlibrary\2014M02\";
libname x151 "\\Outputlibrary\2014M03\";
libname x152 "\\Outputlibrary\2014M04\";
libname x153 "\\Outputlibrary\2014M05\";
libname x154 "\\Outputlibrary\2014M06\";
libname x155 "\\Outputlibrary\2014M07\";
libname x156 "\\Outputlibrary\2014M08\";
libname x157 "\\Outputlibrary\2014M09\";
libname x158 "\\Outputlibrary\2014M10\";
libname x159 "\\Outputlibrary\2014M11\";
libname x160 "\\Outputlibrary\2014M12\";
libname x161 "\\Outputlibrary\2015M01\";
libname x162 "\\Outputlibrary\2015M02\";
libname x163 "\\Outputlibrary\2015M03\";
libname x164 "\\Outputlibrary\2015M04\";
libname x165 "\\Outputlibrary\2015M05\";
libname x166 "\\Outputlibrary\2015M06\";
libname x167 "\\Outputlibrary\2015M07\";
libname x168 "\\Outputlibrary\2015M08\";
libname x169 "\\Outputlibrary\2015M09\";
libname x170 "\\Outputlibrary\2015M10\";
libname x171 "\\Outputlibrary\2015M11\";
libname x172 "\\Outputlibrary\2015M12\";
libname x173 "\\Outputlibrary\2016M01\";
libname x174 "\\Outputlibrary\2016M02\";
libname x175 "\\Outputlibrary\2016M03\";
libname x176 "\\Outputlibrary\2016M04\";
libname x177 "\\Outputlibrary\2016M05\";
libname x178 "\\Outputlibrary\2016M06\";
libname x179 "\\Outputlibrary\2016M07\";
libname x180 "\\Outputlibrary\2016M08\";
libname x181 "\\Outputlibrary\2016M09\";
libname x182 "\\Outputlibrary\2016M10\";
libname x183 "\\Outputlibrary\2016M11\";
libname x184 "\\Outputlibrary\2016M12\";
libname x185 "\\Outputlibrary\2017M01\";
libname x186 "\\Outputlibrary\2017M02\";
libname x187 "\\Outputlibrary\2017M03\";
libname x188 "\\Outputlibrary\2017M04\";
libname x189 "\\Outputlibrary\2017M05\";
libname x190 "\\Outputlibrary\2017M06\";
libname x191 "\\Outputlibrary\2017M07\";
libname x192 "\\Outputlibrary\2017M08\";
libname x193 "\\Outputlibrary\2017M09\";
libname x194 "\\Outputlibrary\2017M10\";
libname x195 "\\Outputlibrary\2017M11\";
libname x196 "\\Outputlibrary\2017M12\";
libname x197 "\\Outputlibrary\2018M01\";
libname x198 "\\Outputlibrary\2018M02\";
libname x199 "\\Outputlibrary\2018M03\";
******Your macro to go through steps;
%macro State (DName,input_data);
data &DName;
set &input_data;
run;
%mend State;
%State(x101.test_data,x0.test_data);
%State(x102.test_data,x1.test_data);
%State(x103.test_data,x2.test_data);
%State(x104.test_data,x3.test_data);
%State(x105.test_data,x4.test_data);
%State(x106.test_data,x5.test_data);
%State(x107.test_data,x6.test_data);
%State(x108.test_data,x7.test_data);
%State(x109.test_data,x8.test_data);
%State(x110.test_data,x9.test_data);
%State(x111.test_data,x10.test_data);
%State(x112.test_data,x11.test_data);
%State(x113.test_data,x12.test_data);
%State(x114.test_data,x13.test_data);
%State(x115.test_data,x14.test_data);
%State(x116.test_data,x15.test_data);
%State(x117.test_data,x16.test_data);
%State(x118.test_data,x17.test_data);
%State(x119.test_data,x18.test_data);
%State(x120.test_data,x19.test_data);
%State(x121.test_data,x20.test_data);
%State(x122.test_data,x21.test_data);
%State(x123.test_data,x22.test_data);
%State(x124.test_data,x23.test_data);
%State(x125.test_data,x24.test_data);
%State(x126.test_data,x25.test_data);
%State(x127.test_data,x26.test_data);
%State(x128.test_data,x27.test_data);
%State(x129.test_data,x28.test_data);
%State(x130.test_data,x29.test_data);
%State(x131.test_data,x30.test_data);
%State(x132.test_data,x31.test_data);
%State(x133.test_data,x32.test_data);
%State(x134.test_data,x33.test_data);
%State(x135.test_data,x34.test_data);
%State(x136.test_data,x35.test_data);
%State(x137.test_data,x36.test_data);
%State(x138.test_data,x37.test_data);
%State(x139.test_data,x38.test_data);
%State(x140.test_data,x39.test_data);
%State(x141.test_data,x40.test_data);
%State(x142.test_data,x41.test_data);
%State(x143.test_data,x42.test_data);
%State(x144.test_data,x43.test_data);
%State(x145.test_data,x44.test_data);
%State(x146.test_data,x45.test_data);
%State(x147.test_data,x46.test_data);
%State(x148.test_data,x47.test_data);
%State(x149.test_data,x48.test_data);
%State(x150.test_data,x49.test_data);
%State(x151.test_data,x50.test_data);
%State(x152.test_data,x51.test_data);
%State(x153.test_data,x52.test_data);
%State(x154.test_data,x53.test_data);
%State(x155.test_data,x54.test_data);
%State(x156.test_data,x55.test_data);
%State(x157.test_data,x56.test_data);
%State(x158.test_data,x57.test_data);
%State(x159.test_data,x58.test_data);
%State(x160.test_data,x59.test_data);
%State(x161.test_data,x60.test_data);
%State(x162.test_data,x61.test_data);
%State(x163.test_data,x62.test_data);
%State(x164.test_data,x63.test_data);
%State(x165.test_data,x64.test_data);
%State(x166.test_data,x65.test_data);
%State(x167.test_data,x66.test_data);
%State(x168.test_data,x67.test_data);
%State(x169.test_data,x68.test_data);
%State(x170.test_data,x69.test_data);
%State(x171.test_data,x70.test_data);
%State(x172.test_data,x71.test_data);
%State(x173.test_data,x72.test_data);
%State(x174.test_data,x73.test_data);
%State(x175.test_data,x74.test_data);
%State(x176.test_data,x75.test_data);
%State(x177.test_data,x76.test_data);
%State(x178.test_data,x77.test_data);
%State(x179.test_data,x78.test_data);
%State(x180.test_data,x79.test_data);
%State(x181.test_data,x80.test_data);
%State(x182.test_data,x81.test_data);
%State(x183.test_data,x82.test_data);
%State(x184.test_data,x83.test_data);
%State(x185.test_data,x84.test_data);
%State(x186.test_data,x85.test_data);
%State(x187.test_data,x86.test_data);
%State(x188.test_data,x87.test_data);
%State(x189.test_data,x88.test_data);
%State(x190.test_data,x89.test_data);
%State(x191.test_data,x90.test_data);
%State(x192.test_data,x91.test_data);
%State(x193.test_data,x92.test_data);
%State(x194.test_data,x93.test_data);
%State(x195.test_data,x94.test_data);
%State(x196.test_data,x95.test_data);
%State(x197.test_data,x96.test_data);
%State(x198.test_data,x97.test_data);
%State(x199.test_data,x98.test_data);
I don't see how you can make it any more efficient.
It looks like there is a pattern to the sub-folder names, so you could make it easier on the programmer.
%macro convert
(member= /* Dataset to move */
,fromdir= /* Top level source directory */
,todir= /* Top level target directory */
,fromdate= /* First month subdirectory */
,todate= /* Last month subdirecctory */
);
%local i month infile outfile ;
%do i=0 to %sysfunc(intck(month,&fromdate,&todate));
%let month=%sysfunc(intnx(month,&fromdate,&i),yymm7);
%let infile=&fromdir\&month\&member..sas7bdat;
%let outfile=&todir\&month\&member..sas7bdat;
data "&outfile";
set "&infile";
* Transformation code codes here ;
run;
%end;
%mend convert;
Tom,
Thanks for this piece of code however, I'm running into some issues.
Should the code look something like this;
I'm unsure how the macro will loop through each month and year?
%macro convert
(member= ESFS0004_PRT /* Dataset to move */
,fromdir= \\InputLib\ /* Top level source directory */
,todir= \\OutputLib\ /* Top level target directory */
,fromdate= 2010/* First month subdirectory */
,todate= 2018 /* Last month subdirecctory */
);
%local i month infile outfile ;
%do i=0 to %sysfunc(intck(month,&fromdate,&todate));
%let month=%sysfunc(intnx(month,&fromdate,&i),yymm7);
%let infile=&fromdir\&month\&member..sas7bdat;
%let outfile=&todir\&month\&member..sas7bdat;
data "&outfile";
set "&infile";
* Transformation code codes here ;
run;
%end;
%mend convert;
You supply the values to a macro when you CALL it, not when you define it.
The macro is expecting valid SAS dates for the from/to date values.
%convert
(member= ESFS0004_PRT /* Dataset to move */
,fromdir= \\InputLib /* Top level source directory */
,todir= \\OutputLib /* Top level target directory */
,fromdate= '01JAN2010'd /* First month subdirectory */
,todate= '01MAY2018'd /* Last month subdirecctory */
);
This code works perfectly now that I understand it.
Thank you very much. Just one edit to be made.
%macro convert
(member= /* Dataset to move */
,fromdir= /* Top level source directory */
,todir= /* Top level target directory */
,fromdate= /* First month subdirectory */
,todate= /* Last month subdirecctory */
);
%local i month infile outfile ;
%do i=0 %to %sysfunc(intck(month,&fromdate,&todate));
%let month=%sysfunc(intnx(month,&fromdate,&i),yymm7);
%let infile=&fromdir\&month\&member..sas7bdat;
%let outfile=&todir\&month\&member..sas7bdat;
data "&outfile";
set "&infile";
* Transformation code codes here ;
run;
%end;
%mend;
%convert;
Kind regards,
Sean
Is there anyway to simplify this type of process.
Simplify the process or simplify the coding??
Yes, a macro loop could simplify the coding of this process.
PROC COPY would run faster than using a data step.
But the process still involves a lot of steps, and I don't see a way to reduce the number of steps.
Consider whether it would be advantageous to change your file structure, going forward. Right now, you are saving data for each month in a separate folder. With slight changes to your naming conventions, you could put them all into a single folder. For example, instead of naming 99 data sets "test_data", they could be named "test_data_YYYYmMM" (with different values for YYYY and MM). Then making copies would be trivial:
proc copy in=in_folder out=out_folder;
select test_data: ;
run;
Or, consider using one folder to hold all the "test_data" data sets only, using names like _M2010M01, _M2010M02, etc.
Given what you have to work with now, the suggestions you have received are good ones!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.