BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
AlexeyS
Pyrite | Level 9

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

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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;

View solution in original post

10 REPLIES 10
Ksharp
Super User

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
Pyrite | Level 9

Thank you very much.

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

 

Ksharp
Super User
%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
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Super User
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
Pyrite | Level 9

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
Super User

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1019 views
  • 2 likes
  • 3 in conversation