Import all excel files with hebrew names from a folder

[AlexeyS]

Hello,

I want to read all excel files(.xlsx) from a one folder. My problem that files have hebrew names, and when i read them filename contains gibberish for the name of files.

The code i use is a follows :

%let subdir=D:\sasdata\;
filename dir pipe "dir &subdir.*.xlsx /B";
data new;
infile dir truncover end=last;
input filename $100.;
filename=cats("&subdir",filename);
run;

Thank you

[Ksharp · Accepted solution]

The following code could get you all the excel file name. After that using 'call execute()' to import these excel file .

%let subdir=c:\temp\  ;


data _null_;
 rc=filename('x',"&subdir");
 did=dopen('x');
 do i=1 to dnum(did);
   filename=dread(did,i);
   put filename=;
   /*call execute(); <- import all the xlsx files*/
 end;
run;

[AlexeyS]

Thank you very much.

But how can i use call execute to import and concatenate them into one dataset?

[Kurt_Bremser]

Make sure that you have UTF session encoding. If the filenames already have Hebrew characters, you can bet that the data will also contain some.

[Ksharp]

%let subdir=c:\temp\  ;

options validvarname=any validmemname=extend;
data _null_;
 rc=filename('x',"&subdir");
 did=dopen('x');
 do i=1 to dnum(did);
   filename=dread(did,i);
   if upcase(scan(filename,-1,'.'))='XLSX' then 
call execute(catt('proc import datafile="&subdir.\',filename,'" out=',scan(filename,1,'.'),' dbms=xlsx replace;run;' ));
 end;
run;

[AlexeyS]

Hi,

I have more one question, can you please help me.

I run this code successfully. But my excel file names consist of name plus date like temp_24122021.

When i read them dynamically i want to save inside dataset also the date from the file name. Which way is more appropriate?

For example,

After  i read excel temp_24122021.xlsx and created a new dataset temp then i want to save inside temp  dataset variable  date=24/12/2021.

Of course i can also to read excel temp_24122021.xlsx and created a new dataset temp_24122021 that contain date, but still the problem doesn't appear.

Thank you very much

[Ksharp]

Sure. Just add some more code in it .

%let subdir=c:\temp\  ;

options validvarname=any validmemname=extend;
data _null_;
 rc=filename('x',"&subdir");
 did=dopen('x');
 do i=1 to dnum(did);
   filename=dread(did,i);
   if upcase(scan(filename,-1,'.'))='XLSX' then do;
call execute(catt('proc import datafile="&subdir.\',filename,'" out=',scan(filename,1,'.'),' dbms=xlsx replace;run;' ));
call execute(cat('data ',scan(filename,1,'.'),';set ',scan(filename,1,'.'),'; 
 date=input("',scan(filename,-1,,'kd'),'",?? ddmmyy12.);format date ddmmyy10.;run;'));
end;
end;
run;

[AlexeyS]

Great decision. Thank you.

One more question. I got an error when i wrote the following code.

call execute(cat('data ', strip(new),';set ', strip(new).';Name=scan(filename,1,'.'); run;'))

After that i want to concatenate all these "new" tables, can i? using call execute?

Thank you

[Ksharp]

OK. Just add prefix before dataset name . Like :

call execute(cat('data _new_',scan(filename,1,'.'),';set ',scan(filename,1,'.'),';


After that , you can set them all by :
data want;
set _new_: ;
run;

[AlexeyS]

Hi,
Maybe I did not explain myself well. I want in same  table called new,  add a new variable named Name that his value should be "first".  My problem, that it doesn't work because of Name variable creation.

filename = "first.xlsx"
call execute(cat('data ', strip(new),';set ', strip(new).';Name=scan(filename,1,'.'); run;'))

Thank you

[Ksharp]

OK. If I understood right. you want make a NEW variable which contain then first part of filename ,right ?

call execute(cat('data _new_',scan(filename,1,'.'),';set ',scan(filename,1,'.'),'; 
 date=input("',scan(filename,-1,,'kd'),'",?? ddmmyy12.);  
 length name $ 200;name="',scan(filename,1,'.'),'";    
 format date ddmmyy10.;run;'));