BookmarkSubscribeRSS Feed
summer7
Calcite | Level 5
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;
6 REPLIES 6
art297
Opal | Level 21
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;
Ksharp
Super User
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
art297
Opal | Level 21
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
Ksharp
Super User
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
summer7
Calcite | Level 5
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
Peter_C
Rhodochrosite | Level 12
"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 . ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 3506 views
  • 0 likes
  • 4 in conversation