topic Re: More efficient macro for reading in from one library and outputting to different library in SAS Enterprise Guide

More efficient macro for reading in from one library and outputting to different library

Sean_OConnor — Thu, 10 May 2018 11:21:36 GMT

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);

Re: More efficient macro for reading in from one library and outputting to different library

Tom — Thu, 10 May 2018 12:10:27 GMT

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;

Re: More efficient macro for reading in from one library and outputting to different library

PaigeMiller — Thu, 10 May 2018 12:13:18 GMT

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.

Re: More efficient macro for reading in from one library and outputting to different library

Astounding — Thu, 10 May 2018 12:29:13 GMT

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!

Re: More efficient macro for reading in from one library and outputting to different library

Sean_OConnor — Thu, 10 May 2018 13:28:46 GMT

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;

Re: More efficient macro for reading in from one library and outputting to different library

Tom — Thu, 10 May 2018 13:41:25 GMT

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 */
);

Re: More efficient macro for reading in from one library and outputting to different library

Sean_OConnor — Thu, 10 May 2018 14:05:58 GMT

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