BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lxn1021
Obsidian | Level 7

Suppose there are multiple EXCEL files under a file path. All the files have irregular names. Each file has multiple sheets, also irregularly named.      Now I want to use PROC SQL to count the number of EXCEL files under the file path, and the number of sheets in each different EXCEL file.    How to do it? Thank you so much!

 

Waiting for the responses~~~

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

If you have the SAS product SAS/ACCESS to PC Files then it is possible to set up a LIBNAME to each Excel workbook and count up the number of sheets using the SAS DICTIONARY table MEMBERS.

 

It would take quite a bit of coding to get it working and it would ignore sheet names longer than 32 characters because that is the maximum SAS can handle via the DICTIONARY mechanism. I would probably take quite a while to run also if you have a lot of workbooks.

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

You can't directly, SAS only looks at SAS related files.  However you can query the underlying Operating System with its commands, and feed the results back into SAS. 

filename mylist pipe 'dir "c:\test\*.*" /b';

data have;

  infile mylist;

  length buffer $2000;

  input buffer;

run;

 

You can then count the observations of dataset have to get number of files.  The filename is a dos command - dir - with a location paramter and /b means basic info - i.e. just a filename.

SASKiwi
PROC Star

If you have the SAS product SAS/ACCESS to PC Files then it is possible to set up a LIBNAME to each Excel workbook and count up the number of sheets using the SAS DICTIONARY table MEMBERS.

 

It would take quite a bit of coding to get it working and it would ignore sheet names longer than 32 characters because that is the maximum SAS can handle via the DICTIONARY mechanism. I would probably take quite a while to run also if you have a lot of workbooks.

PGStats
Opal | Level 21

I tried the following

 

%let path=&sasforum.\datasets;

filename mylist pipe "dir ""&path.\*.xls*"" /b";

data _null_;
  infile mylist truncover;
  length buffer $2000;
  input buffer $2000.;
  i+1;
  length file line $1024;
  file = quote(catx("\", "&path.", buffer));
  lib_name = cats("xl_", i);
  line = catx(" ", "libname", lib_name, "Excel", file, " access=readonly;");
  *put line;
  call execute(line);
run;

and it stopped at libref=XL_64, giving errors after that. So it looks like you have to extract the sheet names one workbook at a time.

PG
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Sorry, I didn't read that about Sheet names.  I would suggest the easiest way to do this would be to use VBA in Excel - use the right tech for the task.  It is a simple bit of code, outside of the scope of this forum however.  But google for:

Open list of files in Excel

http://software-solutions-online.com/2014/03/05/list-files-and-folders-in-a-directory/

 

Then simply loop over that list;

for each cell in range[...]

  open file

  cell.offset(0,1).value=activeworkbook.sheets

  close file

close

 

You then have a list of files, with number of sheets.  You could also output a list of sheet names if you.  VBA is pretty powerfull when using Excel.  Just google anything VBA excel <your topic> and there is lots of code available.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 4 replies
  • 3682 views
  • 5 likes
  • 4 in conversation