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;
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.
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
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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.