SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Import multiple Excel file into SAS, they have the same variables but all placed in different range

Reply
Senior User
Posts: 1

Import multiple Excel file into SAS, they have the same variables but all placed in different range

Hi I'm pretty new with SAS, I have 36 Excel files, each of them have the header placed in different row,  I don't want to open each file in order to specify the range. Is there any shortcut I can use?

 

PROC IMPORT DATAFILE= "C:\Users\File1.xlsx"
OUT= phi.file1
DBMS=excel REPLACE;
getnames=yes;
RANGE="A6:W10000";
run;


PROC IMPORT DATAFILE= "C:\Users\file2.xlsx"
OUT= phi.file2
DBMS=excel REPLACE;
getnames=yes;
RANGE="A9:W10000";
run;

Super User
Super User
Posts: 7,970

Re: Import multiple Excel file into SAS, they have the same variables but all placed in different ra

Not really, you are moving data from an unstructured datasource - Excel - into a structured environment.  If the data doesn't conform to some sort of standard youwill have trouble directly reading it.  Do you have SAS 9.4?  If so then you could try using libname excel:

libname myexcel excel "path_to_your_file\your_file.xlsx";

Then look at the libname myexcel, this may be able to figure it out.  If not you could name the range in each sheet, that way it wouldn't matter where each block started.

Super User
Posts: 5,431

Re: Import multiple Excel file into SAS, they have the same variables but all placed in different ra

If you are new to SAS it would definitely be faster to edit each Excel file rather than trying to solve with clever programming.
And when you're at it, save them as csv.
Data never sleeps
PROC Star
Posts: 7,477

Re: Import multiple Excel file into SAS, they have the same variables but all placed in different ra

If there's a variable that you can check to see if you've reached the header row, then you could macroize (or use call execute) to run something runs something like the following for each of your files:

libname xl xlsx '/folders/myfolders/book1.xlsx';
data have (drop=header);
  header=1;
  do until(last);
    set xl.Sheet1 end=last;
    if header then do;
      if not missing(A) then do;
        header=0;
        output;
      end;
    end;
    else output;
  end;
run;

proc export data=have outfile='/folders/myfolders/temp.csv' dbms=csv replace;
  putnames=no;
run;

proc import datafile='/folders/myfolders/temp.csv' dbms=csv out=book1 replace;
run;

Art, CEO, AnalystFinder.com

Super User
Posts: 19,822

Re: Import multiple Excel file into SAS, they have the same variables but all placed in different ra

You can for the end, but it's harder for the starting variable. If you know the start of the range, then there are ways to specify the end as being large. 

Ask a Question
Discussion stats
  • 4 replies
  • 129 views
  • 1 like
  • 5 in conversation