BookmarkSubscribeRSS Feed
Calcite | Level 5

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);
O codigo roda sem erros porem retorna uma tabela vazia. testei com outras pasta e acontece a mesma coisa.  alguem sabe o motivo?
SAS Employee

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.

Calcite | Level 5

hello, @donricardo 

I did but it didn't work.

SAS Employee

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:  


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.

SAS Employee

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


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



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


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












Calcite | Level 5

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 ?

Calcite | Level 5

the code says that the file does not exist.

What should I do ?

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

SAS Employee

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!

Calcite | Level 5

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?



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1 like
  • 2 in conversation