como listar varios arquivos de excel de uma pasta

[paudauan_level_]

Bom dia!

Pessoal, estou usando o SAS enterprise Guide, criando um program para pegar varios arquivos de excel presente em uma pasta. utilizei o seguinte codigo:

%let caminho = C:\Users\roogomes\Desktop\SAS;

filename dir "%caminho.";

data arquivo;

 fid =  dopen("dir");

if fid > 0 then do;

     contagem = dnum(fid);

      do i = 1 to contagem;

              nome_arquivo = dread(fid, i);

              output;

      end;

end;

run;

O codigo roda sem erros porem retorna uma tabela vazia. testei com outras pasta e acontece a mesma coisa.  alguem sabe o motivo?

[donricardo]

I'll try a few other ideas, but one obvious thing is:   change the FILENAME statement to   Filename dir "&caminho"

your code thinks you're calling a macro definition named caminho, instead of just resolving a macro variable.  See if that gets it going.

[paudauan_level_]

hello, @donricardo 

I did but it didn't work.

[donricardo]

Hi Paudahuan:

I did just test this code and it works perfectly and it will return a list of all files in a given directory, to your log.  here are your steps:

1) submit the macro definition (every line from %macro to %mend

2) When you call the macro definition, in the parenthesis, write the name of the folder containing the .xls and .xlsx files, something like this:   %find_excel(S:\Workshop)

3) Go to your log and observe the list of Excel workbooks; it will look something like this:  

S:\Workshop\custfm.xls
S:\Workshop\custfm.xlsx
S:\Workshop\daily_sales.xls
S:\Workshop\junk.xlsx
S:\Workshop\junk1.xlsx
S:\Workshop\sales.xlsx

4) Depending on how many you have, you can manually import them into SAS using PROC IMPORT.   

5) Alternatively, you could write a libname statement, and use the Data step to read, combine, etc. selected workbooks, something like this:

libname exfile xlsx ("s:\workshop\custfm.xlsx","s:\workshop\junk.xlsx");

I realize this code may be new to you, but at least give it a try, see your results.

[donricardo]

Aqui esta otra opcion ... parece que no necisita un archivo de SAS, vamos a probar un macro, que hace casi el mismo.   Esta macro debe escribir todos los XLS y XLSX archivos a tu SAS log.

%macro find_excel(dir) / minoperator;

%local fileref rc did n memname didc;

%let rc=%sysfunc(filename(fileref,&dir));

%let did=%sysfunc(dopen(&fileref));

%if &did=0 %then

   %do;

       %put ERROR:  Archivo %upcase(&dir) no existe;

       %return;

  %end;

%do n=1 %to %sysfunc(dnum(&did));

    %let memname=%sysfunc(dread(&did,&n));

    %if %upcase(%scan(&memname,-1,.)) in XLS XLSX

         %then %put &dir\&memname;  /*esta se escriba un lista en el log de sus                                                                                         archivos   que termina en XLS o XLSX*/

    %end;

%let didc=%sysfunc(dclose(&did));

%let rc=%sysfunc(filename(fileref));

%mend find_excel;

/*ahora, llama esta macro con el parameter que contiene tus archivos!!*/

%find_excel(c:\mi carpeta cualquiere nombre)  /*no punto y comma para terminarlo*/

[paudauan_level_]

i don't understand very well. I am trying to make a program,  because i need to join to several  excel file and form a table. where do i put this  code ?

[paudauan_level_]

the code says that the file does not exist.

What should I do ?

should I check with IT support to better understand what happened?

[donricardo]

It's hard for me to say without looking at your log.   The code i wrote for you will generate an error note if the file you're searching for does not exist;  so the error message would be expected.   Try it with an Excel file that you KNOW for sure exists.   let me know!

[paudauan_level_]

hello!
I'm sure the file exists. I will send you some prints.
The code that I sent in my initial question, could you tell me why it doesn't show the files in the folder that I specify in the path?