topic Re: Import all excel files with hebrew names from a folder in SAS Programming

Import all excel files with hebrew names from a folder

AlexeyS — Sat, 16 Oct 2021 11:08:38 GMT

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

Re: Import all excel files with hebrew names from a folder

Ksharp — Sat, 16 Oct 2021 11:34:27 GMT

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;

Re: Import all excel files with hebrew names from a folder

Kurt_Bremser — Sat, 16 Oct 2021 13:32:40 GMT

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.

Re: Import all excel files with hebrew names from a folder

AlexeyS — Sat, 16 Oct 2021 17:55:08 GMT

Thank you very much.

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

Re: Import all excel files with hebrew names from a folder

Ksharp — Sun, 17 Oct 2021 11:04:50 GMT

%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;

Re: Import all excel files with hebrew names from a folder

AlexeyS — Fri, 24 Dec 2021 19:06:55 GMT

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

Re: Import all excel files with hebrew names from a folder

Ksharp — Sat, 25 Dec 2021 10:23:12 GMT

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;

Re: Import all excel files with hebrew names from a folder

AlexeyS — Sun, 26 Dec 2021 18:35:49 GMT

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

Re: Import all excel files with hebrew names from a folder

Ksharp — Mon, 27 Dec 2021 11:42:19 GMT

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;

Re: Import all excel files with hebrew names from a folder

AlexeyS — Mon, 27 Dec 2021 12:05:45 GMT

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

Re: Import all excel files with hebrew names from a folder

Ksharp — Mon, 27 Dec 2021 12:20:14 GMT

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;'));