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

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

Sten — Tue, 23 May 2017 15:31:21 GMT

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;

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

RW9 — Tue, 23 May 2017 16:02:11 GMT

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.

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

LinusH — Tue, 23 May 2017 16:08:14 GMT

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.

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

art297 — Tue, 23 May 2017 17:04:39 GMT

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

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

Reeza — Tue, 23 May 2017 19:42:27 GMT

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.