topic Re: como listar varios arquivos de excel de uma pasta in SAS Programming

como listar varios arquivos de excel de uma pasta

paudauan_level_ — Thu, 01 Jun 2023 14:21:46 GMT

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;

run;

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

Re: como listar varios arquivos de excel de uma pasta

donricardo — Thu, 01 Jun 2023 19:12:17 GMT

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.

Re: como listar varios arquivos de excel de uma pasta

paudauan_level_ — Thu, 01 Jun 2023 20:43:41 GMT

hello, @donricardo

I did but it didn't work.

Re: como listar varios arquivos de excel de uma pasta

donricardo — Thu, 01 Jun 2023 22:19:16 GMT

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

Re: como listar varios arquivos de excel de uma pasta

paudauan_level_ — Fri, 02 Jun 2023 12:15:37 GMT

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 ?

Re: como listar varios arquivos de excel de uma pasta

donricardo — Fri, 02 Jun 2023 18:08:49 GMT

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.

Re: como listar varios arquivos de excel de uma pasta

paudauan_level_ — Mon, 05 Jun 2023 12:48:42 GMT

the code says that the file does not exist.

What should I do ?

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

Re: como listar varios arquivos de excel de uma pasta

donricardo — Thu, 08 Jun 2023 15:27:06 GMT

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!

Re: como listar varios arquivos de excel de uma pasta

paudauan_level_ — Fri, 09 Jun 2023 12:25:05 GMT

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?