topic Re: Proc Import - empty spreadsheet in SAS Procedures

Proc Import - empty spreadsheet

summer7 — Mon, 18 Apr 2011 21:10:12 GMT

Hi,

I have several excel workbook with multiple sheets that I'm dynamically loading. I have a couple of the workbooks that have a spreadsheets that are blank. This fails to load correctly as the dataset has no rows but does get created with data issues. I've tried %sysfunc(exist( but doesn't work correctly as the dataset does get created. How can I get this to work? I tried using an array to check and set a variable to skip it also. The array says the array is empty and stops.

data _null_;
set &&sheet&i;
array _c{*} _character_;
array _n{*} _numeric_;
if missing(coalesceC(of _c{*})) and missing(coalesce(of _n{*})) then do;
call symputx('emptyds','Y');
end;
else do;
call symputx('emptyds','N');
end;
run;

Re: Proc Import - empty spreadsheet

art297 — Mon, 18 Apr 2011 21:50:49 GMT

You might be able to solve your problem via using the excel engine in a libname statement. When I tried the following code, a blank spreadsheet was shown with one column, namely F1. As such, the code borrowed from support.sas.com allowed one to see if such a variable exists. As long as your real data doesn't contain such a variable, it might provide the basis of a solution:

libname t1 "c:\test.xls";

data _null_;
dsid=open("t1.'sheet1$'n");
check=varnum(dsid,'f1');
if check=0 then put 'Variable does not exist';
else put 'Variable is located in column ' check +(-1) '.';
run;

HTH,
Art
-----------
> Hi,
>
> I have several excel workbook with multiple sheets
> that I'm dynamically loading. I have a couple of the
> workbooks that have a spreadsheets that are blank.
> This fails to load correctly as the dataset has no
> rows but does get created with data issues. I've
> tried %sysfunc(exist( but doesn't work correctly as
> the dataset does get created. How can I get this to
> work? I tried using an array to check and set a
> variable to skip it also. The array says the array
> is empty and stops.
>
> data _null_;
> set &&sheet&i;
> array _c{*} _character_;
> array _n{*} _numeric_;
> if missing(coalesceC(of _c{*})) and
> missing(coalesce(of _n{*})) then do;
> call symputx('emptyds','Y');
> end;
> else do;
> call symputx('emptyds','N');
> end;
> run;

Re: Proc Import - empty spreadsheet

Ksharp — Tue, 19 Apr 2011 07:37:01 GMT

Maybe You can code like this:
[pre]
libname _xls excel path="c:\t.xls";
proc sql;
select *
from _xls.'Sheet2$'n ;

%put NOTE: The number of obs in Sheet2 is &sqlobs.;
quit;
/*%if &sqlobs. ne 0 %then %do;
proc import......;
%end; */

[/pre]

Ksharp

Re: Proc Import - empty spreadsheet

art297 — Tue, 19 Apr 2011 12:34:57 GMT

Ksharp,

The interesting part about this case is that the record will appear as having one observation even though it, in fact, is an empty spreadsheet.

Art
----------
> Maybe You can code like this:
> [pre]
> libname _xls excel path="c:\t.xls";
> proc sql;
> select *
> from _xls.'Sheet2$'n ;
>
> %put NOTE: The number of obs in Sheet2 is
> &sqlobs.;
> uit;
> /*%if &sqlobs. ne 0 %then %do;
> proc import......;
> %end; */
>
>
>
> [/pre]
>
> Ksharp

Re: Proc Import - empty spreadsheet

Ksharp — Wed, 20 Apr 2011 04:08:31 GMT

Yes.Art.T
I also found this interest thing I have not imagined.
So I try to fix and recode it.
How about this:

[pre]
libname _xls excel path="c:\t.xls" getnames=no mixed=yes scantext=yes ;

proc sql;
select *
from _xls.'Sheet3$'n
where f1 is not missing;

%put NOTE: The number is &sqlobs.;
quit;
[/pre]

Ksharp

Re: Proc Import - empty spreadsheet

summer7 — Tue, 26 Apr 2011 15:20:14 GMT

Thanks all for your suggestions. I did try them all but had unexpected results. I think this was due to the fact that the header row and start row was down several rows instead of the default of 1. This is what I found that worked.

proc import datafile = "c:\temp\test.xls" out = test1(WHERE=(Not(missing(var1) AND missing(var2) AND missing(var3) AND missing(var4)) dbms=xls replace;
sheet = "testit";
namerow = 4;
startrow = 5;
guessingrows=16000;
run;

and then I did a test for if it existed. It would create a blank work dataset. I would test for a zero row count and then ignore it if it was blank.

%if %sysfunc(exist(test1)) %then %do;

proc sql noprint;
select count(*) into :xobs from test1;
quit;
%if &&xobs=0 %then %do;
..... do something ..............

%end; Message was edited by: summer7

Re: Proc Import - empty spreadsheet

Peter_C — Tue, 24 May 2011 22:33:32 GMT

"The array says the array is empty and stops."

extend the definition of the array with constants to guarantee something is present, like
array _c{*} _character_ __empty ; retain __empty ' ' ;
array _n{*} _numeric_ __empN ; retain __empN . ;