Libnames in a DO LOOP? Possible??

[Wouter]

Hi,

I've got the following problem. I want to import xls files, and save them as csv files. These xls files (and processed csv files) are located in folders with subtitles as year (&YEAR) and month (&MONTH). This is no problem, for this, I use the code below. But... I want to have this more dynamically; I want to process multiple files at the same time.

For this, the libname has to change. A sort of do loop for the libname, but I don't have ANY clue how to do this...

By the way; I need to use the libname. Replacing "set x.'sheet1$'n;" with the full pathname will not work in the code below.

I guess I have to do something with a macro, but CAN libnames be used in a sort of loop?

Let's say I want to proces month 9 of 2009 until month 11 of 2010. Call Symput but then...?

Thanks for your attention at the first place!!!


libname x "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS";

data_null_;
set x.'sheet1$'n;
file "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS" dsd;
put (_all_) (:);
run;

libname x clear;

[ArtC]

There is a LIBNAME function for use within the DATA step.

[Wouter]

Thanks for your answer. But are you saying the following will work??

data_null_;

<<

libname x "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS";
set x.'sheet1$'n;
file "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS" dsd;
put (_all_) (:);
libname x clear;

<<

run;

[ArtC]

Well it will work, but it will not do what you want.

You are using the LIBNAME statement, which is a global statement and will be executed once. The LIBNAME function, otoh, is executable and will be executed once for each observation on you incoming data.

There are other issues in your code in addition to the original question. If you are going to read the XLS file directly into the DATA step, you will need to use the EXCEL engine.

If I can guess at your larger task, it looks like a macro loop surrounding a series of PROC IMPORT steps might be closer. To be more helpful I need to know more about the full task.

[Wouter]

Hi,

I've made use of this code, because it was not possible (that is, I tried multiple options) to import a XLS file and let it save automatically as a CSV file (he DID import the XLS, but I had to manually save the CVS (choose directory etc)).

The task I want to peform is: I want to create a report, in which 2 dates are given: the FROM and the END date. For every date (month end), there are directories. Every dir is filled with one XLS (same name for every month). What I want is to automatically process all those XLS files --> load them into SAS and let them save in the directories where they belong, but as a CSV file. Sounds simple, and maybe it is with "otoh"?

[SPR]

Hello Wouter,

I think that a macro would be better for you because you need to loop over macro variables &Year and &Month. It should be something like this:
[pre]
%macro a;
%do i=1 %to 2;
%let year=%SYSEVALF(2000+&i);
libname x "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS";
set x.'sheet1$'n;
file "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS" dsd;
put (_all_) (:);
libname x clear;
%end;
%mend a;
%a;
[/pre]
Sincerely,
SPR

[Wouter]

Hi, thanks for your answer! I've created a macro around the original global statement and data step. And... it works, but only partly...! When I put begin month 08 and end month 10 for 2010 in the code (see below: %RUNYYMM(201008, 201010);), the program performs 3 steps, but only for month 10!

The strange thing is, the following statements situated in the code (the %put codes) DO generate the right dates in the log:

%PUT "YYYYMM IS " &YYYYMM;
%PUT "MMDDYYYY IS " &MMDDYYYY;
%PUT "THIS IS LOOP " &I;

For every of the 3 steps, the code above generates the right dates and the itteration of the steps --> in this case 3, because I've choosen for 2010-08 to 2010-10.

So the question now is... How can I get the month number and the year number from &YYYYMM (or &MMDDYYYY) and will this work?

THe code as it is now:

%MACRO RUNYYMM(BEG_YYMM,END_YYMM);
%LET BEG_YY = %EVAL(%SUBSTR(&BEG_YYMM,1,4) * 1);
%LET BEG_MM = %EVAL(%SUBSTR(&BEG_YYMM,5,2) * 1);
%LET END_YY = %EVAL(%SUBSTR(&END_YYMM,1,4) * 1);
%LET END_MM = %EVAL(%SUBSTR(&END_YYMM,5,2) * 1);
*GET ROUNDS OF THE LOOP;
%LET L=%EVAL((&END_YY-&BEG_YY)*12 + (&END_MM - &BEG_MM) + 1);
*GENERATE THE DATE CONSTANTS, THESE ARE GLOBAL VARIABLES;
%DO I = 1 %TO &L;
DATA _NULL_;
/* Ways to generate date MACRO VARIABLES*/
CALL SYMPUT('YYYYMM', PUT(INTNX('MONTH',MDY(&BEG_MM,1,&BEG_YY),&I - 1,'B'),YYMMN6.));
CALL SYMPUT('MMDDYYYY', "'"||PUT(INTNX('MONTH',MDY(&BEG_MM,1,&BEG_YY),&I - 1,'E'),MMDDYYN8.)||"'");
run;
%LET YEAR= %EVAL(%SUBSTR(&BEG_YYMM,1,4) * 1);
%LET MONTH = %EVAL(%SUBSTR(&BEG_YYMM,3,2) * 1);
*PUT THE DATE CONSTANTS IN THE LOG;
%PUT "YYYYMM IS " &YYYYMM;
%PUT "MMDDYYYY IS " &MMDDYYYY;
%PUT "THIS IS LOOP " &I;

*Original code;
libname x "D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.XLS";
data _null_;
set x.'sheet1$'n;
file
"D:\REPORTS\XLS\&YEAR\&MONTH\REPORT.CSV" dsd;
put (_all_) (:);
run;
libname x clear;

%END
;
RUN;
%MEND
;
%RUNYYMM(201008, 201010);

[SPR]

Wouther,

There is a more convenient way to deal with dates using 2 loops: one for years and one for months like this:
[pre]
%macro a(ds,de);
%local m m0 m1 ms me y ys ye date;
%let ms=%SUBSTR(&ds,1,2);
%let me=%SUBSTR(&de,1,2);
%let ys=%SUBSTR(&ds,5);
%let ye=%SUBSTR(&de,5);
%put A: ys=&ys ms=&ms ys=&ys ye=&ye;
%do y=&ys %to &ye;
%if &y GT &ye %then %let m0=1;
%else %let m0=&ms;
%if &y LT &ye %then %let m1=12;
%else %let m1=&me;
%do m=&ms %to &me;
;
%let date=%SYSFUNC(PUTN(%SYSFUNC(MDY(&m,01,&y)),date7.));
%put A: y=&y m=&m date=&date;
%end;
%end;
%mend a;
%a(08152008,10012010);
[\pre]
SPR

[SPR]

Sorry, there were some mistakes in my code. The right one is here:
[pre]
%macro a(ds,de);
%let ms=%SUBSTR(&ds,1,2);
%let me=%SUBSTR(&de,1,2);
%let ys=%SUBSTR(&ds,5);
%let ye=%SUBSTR(&de,5);
%put A: ys=&ys ms=&ms ys=&ys ye=&ye;
%do y=&ys %to &ye;
%if &y EQ &ys %then %let m0=&ms;
%else %let m0=1;
%if &y LT &ye %then %let m1=12;
%else %let m1=&me;
%do m=&m0 %to &m1;

%let date=%SYSFUNC(PUTN(%SYSFUNC(MDY(&m,01,&y)),date7.));
%put A: y=&y m=&m date=&date;
%end;
%end;
%mend a;
%a(08152008,10012010);
[/pre]
SPR

[Wouter]

Thank you! I'll test this one on Monday and give you some feedback. I think this code can affect (t+1) the global statement libname?

[Wouter]

Hi,

It COULD be this code worked (well, I am almost sure, because running the code without my code in it creates a beautiful set of dates (from month to month), but it created several error statements (libname not assigned and an Error in the libname) when I incorporated my own code. I adapted the original code with the other statements you've given, and the new, following code works (I changed thedirectory names for private (work...) purposes)!!

%MACRO RUNYYMM(BEG_YYMM,END_YYMM);
%LET BEG_YY = %EVAL(%SUBSTR(&BEG_YYMM,1,4) * 1);
%LET BEG_MM = %EVAL(%SUBSTR(&BEG_YYMM,5,2) * 1);
%LET END_YY = %EVAL(%SUBSTR(&END_YYMM,1,4) * 1);
%LET END_MM = %EVAL(%SUBSTR(&END_YYMM,5,2) * 1);
*GET ROUNDS OF THE LOOP;
%LET L=%EVAL((&END_YY-&BEG_YY)*12 + (&END_MM - &BEG_MM) + 1);
*GENERATE THE DATE CONSTANTS, THESE ARE GLOBAL VARIABLES;
%DO I = 1 %TO &L;
DATA _NULL_;
/* Ways to generate date MACRO VARIABLES*/
CALL SYMPUT('YYYYMM', PUT(INTNX('MONTH',MDY(&BEG_MM,1,&BEG_YY),&I - 1,'B'),YYMMN6.));
CALL SYMPUT('MMDDYYYY', "'"||PUT(INTNX('MONTH',MDY(&BEG_MM,1,&BEG_YY),&I - 1,'E'),MMDDYYN8.)||"'");
run;
%let Jaar=%SUBSTR(&YYYYMM,1,4);
%let Maand=%SUBSTR(&YYYYMM,5);
*PUT THE DATE CONSTANTS IN THE LOG;
%PUT "YYYYMM IS " &YYYYMM;
%PUT "MMDDYYYY IS " &MMDDYYYY;
%PUT "THIS IS LOOP " &I;
libname x "N:\Monthly Reports\\NEW REPORT\XLS\&Jaar\&Maand\mnf.xls";
data _null_;
set x.'sheet1$'n;
file "N:\Monthly Reports\NEW REPORT\&jaar\&maand\mnf.csv" dsd;
put (_all_) (:);
run;
libname x clear;
%END;
RUN;
%MEND;

Thank you all for your input!! It helped me a lot!

[SPR]

Hello Wouter,

As to your original question: "So the question now is... How can I get the month number and the year number from &YYYYMM (or &MMDDYYYY) and will this work?" The answer is the code:
[pre]
%let Y=%SUBSTR(&YYYYMM,1,4);
%let M=%SUBSTR(&YYYYMM,5);
%put RUNYYMM: Y=&y m=&m;
[/pre]
Sincerely,
SPR

[Ksharp]

Hi.
I have another resolution.
Using command 'dir c:\temp\*.xls /s' which can display all the xls files in the temp directory and its all the sub-directory. then using FILENAME and PIPE to get the directory and filename.But each xls file must has unique name to avoid erase the dataset with same name.


[pre]
*To import multi xls files from multi sub-directory.Suppose all the sub-directory with xls files are in the 'c:\temp\';



filename _xls pipe 'dir c:\temp\*.xls /s';
data xls_file;
infile _xls length=len;
input whole $varying200. len;
retain directory;
length file_name $ 200;
if strip(whole) eq: 'c:\temp' then directory=scan(whole,1,' ');
else if scan(whole,-1,'.') eq 'xls' then do;
_name=scan(trim(whole),-1,' ');
file_name=catx('\',directory,_name);
output;
end;

keep file_name;
run;
data _null_;
set xls_file nobs=num_obs;
call symputx(cats('path',_n_),file_name);
call symputx(cats('name',_n_),scan(file_name,-2,'.\'));
call symputx('nobs',num_obs);
run;
%put _user_;
options mprint mlogic symbolgen;
%macro xls_to_csv;
%do i=1 %to &nobs;
proc import datafile="&&path&i" out=&&name&i dbms=excel replace;
getnames=yes;
mixed=yes;
run;
proc export data=&&name&i outfile="%scan(&&path&i,1,'.').csv " dbms=csv replace;run;
%end;
%mend ;

%xls_to_csv

[/pre]



Ksharp