SAS import xls only when columns have a name -

i have excel file needs imported periodically sas. names of columns in row 2 , number of columns can change. i'm using following query:

proc import file = "file.xlsx" out = sasfile dbms= excel replace; sheet = "sheet1"; range = "sheet1$a2:be2000"; getnames = yes; run; 

however, keep getting f variables in sas output. how can dynamically input columns have names?

are saying if column doesn't have name in second row want remove column resulting table?

it bit of pain proc import read xlsx file not formatted table since not support namerow, startrow, datarow, etc. might able reading names , data separately.

first let's create macro variables make solution easy modify.

%let sheetname=sheet1; %let startrow=2; %let lastrow=2000; %let startcol=a; %let lastcol=be; 

now let's read in variable names &startrow.

proc import datafile='c:\users\abernathyt\downloads\book1.xlsx' replace   dbms=xlsx out=names1;   range="&sheetname.$&startcol.&startrow:&lastcol.&startrow";   getnames=no; run; 

and transpose it.

proc transpose data=names1 out=names2;   var _all_; run; 

now let's generate old=new pairs columns want rename , list of columns want drop.

proc sql noprint ;   select case when col1 ne ' ' catx('=',_name_,nliteral(trim(col1))) else ' ' end        , case when col1 ne ' ' ' ' else _name_ end   :rename separated ' '      , :drop separated ' '   names2   ; quit; 

now let's read in data , add dataset options rename and/or drop columns on way out.

proc import datafile='c:\users\abernathyt\downloads\book1.xlsx' replace   dbms=xlsx out=want(rename=(&rename) drop=&drop) ;   range="&sheetname.$&startcol.%eval(&startrow+1):&lastcol.&lastrow";   getnames=no; run;