topic Detect if Excel file for import has blank first row in SAS Programming

Detect if Excel file for import has blank first row

imead — Thu, 05 May 2022 17:48:05 GMT

I have an excel sheet that is sometimes sent to me with a blank first row (2nd row with column names) and other times with a normal format of the first row having the column names. These are the only two ways the file is provided to me (for now, this vendor loves to surprise me). I need this report to remain automated, so I would like to find a way to detect if the first row is blank and if it is then use the proc import range option to correctly start at row two for the column names. If the first row contains the column names then run normally.

I just cannot work out a tidy way of detecting if the first row is blank, I was considering using %IF and %DATATYPE macros, but I haven't tried to do something quite like this before in SAS and not finding something similar enough to adapt for my uses. I'm sure I'm overthinking this too. I would be grateful for any suggestions!

Re: Detect if Excel file for import has blank first row

Tom — Thu, 05 May 2022 18:15:14 GMT

Can you be more specific about the type of "sheet" you are receiving.

Is it an XLSX workbook? Or an XLS workbook? If one of these does it contain multiple sheets or just on sheet?

Or are perhaps getting a CSV file, which is not an Excel file but instead is just a text file.

Other than the extra row does the file change its structure? That is does it always have the same number of columns?

If it is an Excel file then you will have to IMPORT the file first before you can detect if there are any issues.

You could easily use the RANGE option as you say to import just the first cell and then test if that is cell is empty or not.

proc import out=test replace dbms=xlsx datafile='test.xlsx' replace;
  getnames=NO;
  range='$A1:A1';
run;

Now use that to define the RANGE to read.

data _null_;
  set test ;
  put A=;
  call symputx('range',cats('$A',1+(A=' '),':'));
run;

%put &=range;

proc import out=want replace dbms=xlsx datafile='test.xlsx' replace;
  getnames=YES;
  range="&range";
run;

Re: Detect if Excel file for import has blank first row

imead — Thu, 05 May 2022 18:58:26 GMT

I am so sorry I should have specified .xls. I always forget some kind of relevant information.

Your solution works beautifully! Thank you very much.