05-23-2017 11:31 AM
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"
PROC IMPORT DATAFILE= "C:\Users\file2.xlsx"
05-23-2017 12:02 PM
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.
05-23-2017 12:08 PM
05-23-2017 01:04 PM
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
05-23-2017 03:42 PM
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.