BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ksmit
Obsidian | Level 7

Hi,

I have a series of batch programs that terminate due to "ERROR: Couldn't find range or sheet in spreadsheet". What's happening is my xlsx import is seeking 6 potential tabs from the files in the FTP directory. If the file does not have the tab i get the error during import. 

 

Is there an OPTION to bypass this error?

 

I have tried the NOSYNTAXCHECK and NOERRORABEND prior to my data step and still get the error in log. 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksmit
Obsidian | Level 7
Thanks Tom. This helped me spot where my issue lied. Due to not all files having relavant tab names and those with tab_names often having zero obs was seeing unsynchronised file names based on load iteration. File name associated with table and that associated with contents weren't matching when the file didnt have the proper tab so it would break with "data doesn't exist" error.

My Solution was to do nested due loops within a macro theat calls the Tab name : %macro alias(PROCESSED) and added this code based on obs count:
/*Get the member names in the library */
proc contents data=indata._all_ out=work.contents noprint;run;

/*accounts for tab iterations with null observations*/
data _null_;
call symput('obscnt',0);
set work.contents;
where memname in ("&tab_name");
call symput('obscnt',_n_);
stop;
run;

%If &obscnt=0 %then %do;
%put 'There are no records in a dataset.';
%end;
%else %do;

proc sql noprint;
create table work.contents_with_file_name as
select distinct memname, "&file_name" LENGTH=200 as file_name_contents from work.contents
where memname in ("&tab_name");
;quit;

%if &i = 1 %then %do;
data append_contents;
set contents_with_file_name;
run;
%end;
%else %do;
data append_contents;
set append_contents contents_with_file_name;
run;
%end;

THis got me to where I was able to compile datasets without null values into the final output for that single tab name. I followed by calling the macro separate times based on the subsequent tab names.

Thanks so much for your help as it pointed me in the right direction.

View solution in original post

12 REPLIES 12
TomKari
Onyx | Level 15

The typical solution for this problem is to pre-check your incoming data to figure out exactly what it contains, and then adapt your software to only grab that data.

 

Tom

Ksmit
Obsidian | Level 7
Thanks. So, I have a need for imported xlsx files to compile a flat file from data associated with 6 specific tabs (there is potentially 11 tabs worth of data).

I have set up the FTP directory and have the following code to apply memname as sheet name:

/*create library for xlsx file*/
libname indata xlsx "/basedata/sasuser1/********/&file_name";

/*run proc contents on library*/
proc contents data=indata._all_ out=work.contents noprint;
/*store first sheet name in macro variable*/
data _null_;
set contents (keep=memname);
call symput('sheet_name',memname);
run;


/*store results in datasets*/

data file_src_&i (drop=Tries ID);
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count = &i;
run;
This code doesn't render an error, however, the contents table applies to only one file at a time and limits the memnames to only those in the one file.

What I ended up doing was writing the 6 data steps where the sheet name is called directly. See below example:
data file_PROCESSED_src_&i (drop=Tries ID);
length file_name $200. file_path $200.;
set indata.PROCESSED;
sheet='PROCESSED';
file_name = "&file_name";
file_path = "&folder_path";
file_count = &i;
run;

This works. It populates all the data associated with PROCESSED tabs, however, if the PROCESSED tab doesn't exist it renders the error, breaks the batch.

Any help is greatly apprecited.
TomKari
Onyx | Level 15

Here's some code that will hopefully help.

 

1. I switched a SQL call to the DICTIONARY table for your use of PROC CONTENTS. Probably not much difference, I'm just more familiar with it.

2. If I understand what you're trying to do, the key is to run a macro for as many occurences as you have worksheets.

 

Good luck!

   Tom

 

%let i = 1;
%let file_name = ABCD;
%let folder_path = DEFG;

/*create library for xlsx file*/
libname indata xlsx "C:\workingfolder\TestXL.xlsx";

/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname from dictionary.members where libname="INDATA";
run;

%macro GetSheet(sheet_name, i);
data file_src_&i (drop=Tries ID);
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count = &i;
run;
%mend;

data _null_;
set contents (keep=memname);
ExecuteLine = '%GetSheet('||strip(memname)||','||strip(put(_n_, best15.))||');';
call execute(ExecuteLine);
run;
Ksmit
Obsidian | Level 7
Thanks Tom. I worked in your code and I don't get an error. What is happening though is the code is only puling 1 file into SAS, and not the complete file with all tabs. It applies the file_count (&I) to each separate sheet but it is the same data in each. So where sheet name = "A" I have the same data as sheet name "B" but file count is 1 in "A" and 2 in "B".

Thoughts?
TomKari
Onyx | Level 15

Hi, @Ksmit 

Nope, my mind is completely blank (as usual!)

Oddly enough, I had just copied the first sheet into the other two, so I wasn't even able to tell if that was happening with me! But I've modified the data, and the code seems to be working fine in my environment.

Here's a few thoughts.

Here's a modifed version of the last "data _null_" step, where it keeps the results. In my case, the "LinesToSubmit" dataset looks like this

SASSHEET1 %GetSheet(SASSHEET1,1);
SASSHEET2 %GetSheet(SASSHEET2,2);
SASSHEET3 %GetSheet(SASSHEET3,3);

which is exactly what I would expect.

data LinesToSubmit;
set contents (keep=memname);
ExecuteLine = '%GetSheet('||strip(memname)||','||strip(put(_n_, best15.))||');';
call execute(ExecuteLine);
run;

See what it looks like in your case.

Also, I'm attaching the Excel file that I'm using. If you're comfortable opening it, you can take a look and see if I've misunderstood something that you're trying to do.

Can't wait to hear,

   Tom

 

 

 

 

Ksmit
Obsidian | Level 7
Tom,
This works like a charm for one file. Where I am struggling is that I have multiple files to bring in and run through this process. I'm thinking I need to add a %do loop to the process build but am coming up short with the syntax/logic.

I added path from contents. This is same as file_name so it labels each Line with the file_name:
/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname, substr(path,29,200) as path from dictionary.members where libname="INDATA";
run;

This call of the macro:
data submittedlines;
set contents (keep=memname path);
ExecuteLine = '%GetSheet('||strip(path)||','||strip(memname)||','||strip(put(_n_, best15.))||');';
call execute(ExecuteLine);
run;
Brings back all Tabs within last file in directory (# 11 of 11 files in the directory ).



This code:
data submittedlines;
set contents (keep=memname path);
ExecuteLine = '%GetSheet('||strip(path)||','||strip(memname)||');';
call execute(ExecuteLine);
run;
Brings back the last populated tab but processes through all 11 files in directory.

My thoughts are that I need to loop the whole process once per file count. Any suggestions?

Kyle


/*clear libref*/
libname indata clear;
TomKari
Onyx | Level 15

Okay, now it's becoming clearer. If I understand correctly, you want to do this process, but for multiple Excel workbooks. So we need to repeat the line

 

     libname indata xlsx "C:\workingfolder\TestXL.xlsx";

 

for multiple Windows files. Please correct me if I'm wrong.

 

First question; how do you know the names of all of the workbooks that you want to retrieve data from?

Tom

Ksmit
Obsidian | Level 7
You are correct.The filenames are pulled from the defined directory with the following data step:

data all_files;
infile ftp_dir;
input;
file_name = _INFILE_;
file_path = strip(file_name);
if find(file_name,".xlsx") > 0 and find(file_name,"~") = 0;
run;
TomKari
Onyx | Level 15

When I saw you're first post, I heard a little voice in my head saying "Don't reply! Don't reply!". 😂

 

Okay, here's an attempt to loop through multiple workbooks, and then to loop through multiple spreadsheets within the workbooks. Give it a spin, and hopefully it'll give you some thoughts that will put you on the right path.

 

Good luck!

   Tom

 

data all_files;
length file_name file_path $500;
file_path = "C:\workingfolder";
input file_name;
cards;
TestXL1.xlsx
TestXL2.xlsx
TestXL3.xlsx
run;

%macro GetFile(folder_path, file_name, i);

%macro GetSheet(folder_path, file_name, sheet_name, i, j);
data file_src_&i._&j.;
length file_name $200. file_path $200.;
set indata."&sheet_name"n;
sheet="&sheet_name";
file_name = "&file_name";
file_path = "&folder_path";
file_count1 = &i;
file_count2 = &j;
run;
%mend;

/*create library for xlsx file*/
libname indata xlsx "&folder_path.\&file_name.";

/* Get the member names in the library */
proc sql noprint;
create table work.contents as
select memname from dictionary.members where libname="INDATA";
quit;

data SheetsToSubmit;
length ExecuteLine $500;
set contents (keep=memname);
ExecuteLine = '%GetSheet(' || strip("&folder_path.") || ',' || strip("&file_name.") || ',' || strip(memname) || ',' || strip("&i.") || ',' || strip(put(_n_, best15.)) || ');';
call execute(ExecuteLine);
run;
%mend;

data BooksToSubmit;
length ExecuteLine $500;
set all_files;
ExecuteLine = '%GetFile(' || strip(file_path) || ',' || strip(file_name) || ',' || strip(put(_n_, best15.)) || ');';
call execute(ExecuteLine);
run;
Ksmit
Obsidian | Level 7
Thanks Tom. This helped me spot where my issue lied. Due to not all files having relavant tab names and those with tab_names often having zero obs was seeing unsynchronised file names based on load iteration. File name associated with table and that associated with contents weren't matching when the file didnt have the proper tab so it would break with "data doesn't exist" error.

My Solution was to do nested due loops within a macro theat calls the Tab name : %macro alias(PROCESSED) and added this code based on obs count:
/*Get the member names in the library */
proc contents data=indata._all_ out=work.contents noprint;run;

/*accounts for tab iterations with null observations*/
data _null_;
call symput('obscnt',0);
set work.contents;
where memname in ("&tab_name");
call symput('obscnt',_n_);
stop;
run;

%If &obscnt=0 %then %do;
%put 'There are no records in a dataset.';
%end;
%else %do;

proc sql noprint;
create table work.contents_with_file_name as
select distinct memname, "&file_name" LENGTH=200 as file_name_contents from work.contents
where memname in ("&tab_name");
;quit;

%if &i = 1 %then %do;
data append_contents;
set contents_with_file_name;
run;
%end;
%else %do;
data append_contents;
set append_contents contents_with_file_name;
run;
%end;

THis got me to where I was able to compile datasets without null values into the final output for that single tab name. I followed by calling the macro separate times based on the subsequent tab names.

Thanks so much for your help as it pointed me in the right direction.
TomKari
Onyx | Level 15

Very pleased that you got to where you needed to go!

Tom

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 12 replies
  • 4255 views
  • 0 likes
  • 3 in conversation