BookmarkSubscribeRSS Feed
Sten
Calcite | Level 5

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;

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

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.

LinusH
Tourmaline | Level 20
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
art297
Opal | Level 21

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

Reeza
Super User

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. 

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 922 views
  • 1 like
  • 5 in conversation