To do this you need to save the Excel sheet as CSV, and read that using the guessingrows option. To do that within SAS I made a macro using VBScript. It exports the sheet as CSV in the Windows 'Temp' directory, reads it into SAS, and deletes the temporary files (script and csv file) . read/write the CSV file with semicolon delimiter, which is the default format in my country. If your national settings use comma, you have to change the delimiter in PROC Import into ','. * Syntax: %ReadXLasCSV (<Excel path + filename>, <Excet worksheet name>,< Name SAS dataset>); %macro ReadXLasCSV(XLFile, Sheet, Dataset); option noxwait xsync; data _null_; temp = sysget('temp'); * Put the environment variable %temp% in SAS \ variable temp call symput ('temp', right(trim(temp))); * put the content of temp in the SAS macrovariable &temp; run; data _null_; File "&temp.\VBscript.vbs"; put 'Option Explicit'; put 'Dim oExcel'; put 'Set oExcel = WScript.CreateObject("Excel.Application")'; put 'Dim oBook'; s1 = 'Set oBook = oExcel.Workbooks.Open("' || "&XLname" ||'")'; put S1; S2= 'oBook.worksheets("' || "&sheet" || '").activate'; put S2; S3 = 'oBook.SaveAs "'||"&temp.\CSV.CSV"||'",6,,,,,,,,,,True'; put s3; put 'oBook.Close False'; put 'oExcel.Quit'; put 'Set oExcel = Nothing'; run; data _null_; x "&temp.\VBscript.vbs"; run; PROC IMPORT OUT= &dataset DATAFILE= "&temp./csv.csv" DBMS=CSV REPLACE; DELIMITER=';'; GETNAMES=YES; DATAROW=2; guessingrows=MAX; RUN; data _null_; fname="tempfile"; rc=filename(fname, "&temp./csv.csv"); if rc = 0 and fexist(fname) then rc=fdelete(fname); rc=filename(fname); fname="tempfile"; rc=filename(fname, "&temp.\VBscript.vbs"); if rc = 0 and fexist(fname) then rc=fdelete(fname); rc=filename(fname); run; %mend;
... View more
