Import multiple excel workbooks(.xlsx) with same worksheets in LINUX into a SAS dataset

Reply
Contributor
Posts: 55

Import multiple excel workbooks(.xlsx) with same worksheets in LINUX into a SAS dataset

[ Edited ]

Hi,

 

Please help me to write a code to Import multiple excel workbooks(.xlsx) with same worksheets in Linux into a SAS dataset. I have tried PROC IMPORT but it works only for a workbook. I have tried many macros in SAS communities which are not helpful, please help me with macro which loops the files to complete this job

 

PROC IMPORT DATAFILE= "/path/*.xlsx" 
OUT= FILES_CONSOLIDATED
DBMS=XLSX
REPLACE;
SHEET="Sheet1"; 
GETNAMES=YES;
DATAROW= 2;
RUN;

 

Super User
Posts: 3,256

Re: Import multiple excel workbooks(.xlsx) with same worksheets in LINUX into a SAS dataset

Posted in reply to jayakumarmm

Check out this sample SAS macro for reading all CSVs in a directory. With a bit of tweaking it should work for reading same sheet in each Excel workbook:

 

https://support.sas.com/documentation/cdl/en/mcrolref/67912/HTML/default/viewer.htm#n0ctmldxf23ixtn1...

 

 

Super User
Posts: 19,822

Re: Import multiple excel workbooks(.xlsx) with same worksheets in LINUX into a SAS dataset

Posted in reply to jayakumarmm

You can't use wildcards to read multiple XLSX files. You need to generate multiple proc import statements and there are a multitude of ways to do so. 

 

If you search on here you'll find many examples. It doesn't matter than your on Linux, the process is the same. 

Get a list of files

import each file. 

Ask a Question
Discussion stats
  • 2 replies
  • 434 views
  • 0 likes
  • 3 in conversation