topic Re: Get first created sheet name of excel in SAS Programming

Get first created sheet name of excel

pooja86kaushal — Wed, 23 Dec 2020 23:43:48 GMT

Hi , Plz help with the below scenario

I wan to know the first Sheet name (order of creation) in an excel sheet. Is there any way.

Somebody suggested using XML (i.e consider excel as xml)

Use the below code but getting error:

Code:

filename src ZIP '‪C:\Users\pooja\Desktop\Try2.xlsx' member='xl/workbook.xml';
filename des temp;

data _null_;
rc=fcopy('src','des');
run;

filename srcmap temp;
libname des xmlv2 xmlmap=srcmap automap=reuse;

data sheet_names;
set des.sheet;
run;

proc print width= min;
run;

Error:

libname des xmlv2 xmlmap=srcmap automap=reuse;
ERROR: With the AUTOMAP= option, the specified XML document must exist.
ERROR: Error in the LIBNAME statement.

Re: Get first created sheet name of excel

japelin — Thu, 24 Dec 2020 02:00:08 GMT

Do you have '‪C:\Users\pooja\Desktop\Try2.xlsx'?

I tried to submit your code, I got success and error;

If xlsx file is exists and not double byte character in filename , I got success.

If xlsx file is not exists or exists double byte character(I tried Japanese, maybe other than language is same) in filename, I got same error.

can you try this code?

filename foo ZIP 'C:\Users\pooja\Desktop\Try2.xlsx' member='xl/workbook.xml';

data _null_;
infile foo;
input;
put _infile_;
run;

If zip access method got success, you get xml contens in log window.

If you got error "Open failure for XXXXX during attempt to create a local filehandle.", you cannot access your xlsx file.

Then please try a new excel file.

Re: Get first created sheet name of excel

Ksharp — Thu, 24 Dec 2020 13:49:11 GMT

I also want know how to get it .

Re: Get first created sheet name of excel

Tom — Thu, 24 Dec 2020 14:42:00 GMT

Not sure why the FILENAME statement is having trouble. Why not just use the INFILE statement directly?

filename des temp;
data _null_;
  infile '‪C:\Users\pooja\Desktop\Try2.xlsx' zip member='xl/workbook.xml' recfm=n;
  file des recfm=n;
  input;
  put _infile_;
run;
filename srcmap temp;
libname des xmlv2 xmlmap=srcmap automap=reuse;

data sheet_names;
set des.sheet;
run;

proc print width= min;
run;

Re: Get first created sheet name of excel

Ksharp — Fri, 25 Dec 2020 11:38:51 GMT

Tom,
whether there is another way to get it or not? i.e. My sas is 9.2 or 9.3

Re: Get first created sheet name of excel

Ksharp — Fri, 25 Dec 2020 14:09:21 GMT

Maybe the following code is ugly and not right solution. But could give it a try .

I just compare the variable name in the FIRST sheet. If same ,then take it as the first sheet name.

options validvarname=any validmemname=extend;

proc import datafile='c:\temp\temp.xlsx' out=_temp_ dbms=xlsx replace;
getnames=yes;
run;

libname x excel 'c:\temp\temp.xlsx';
proc copy in=x out=work;
run;

proc sql noprint;
select quote(name) into : list separated by ' '
 from dictionary.columns
  where libname='WORK' and memname='_TEMP_';

select count(*) into : n
 from dictionary.columns
  where libname='WORK' and memname='_TEMP_';

create table want as
select *
 from dictionary.columns
  where libname='WORK' and memname ne '_TEMP_'
   group by memname 
    having &n.=sum(name in (&list.));

quit;

title 'First Sheet Name:';
proc sql;
select distinct memname from want;
quit;

libname x clear;

Re: Get first created sheet name of excel

Tom — Fri, 25 Dec 2020 16:53:27 GMT

@Ksharp wrote:
Tom,
whether there is another way to get it or not? i.e. My sas is 9.2 or 9.3

If you are using an old version of SAS that does not support the ZIP libname engine then use your local operating system command for extracting files from a ZIP file to get the XML file with the list of sheets. If your old version of SAS does not support reading XML then just parse the text of the file yourself with a data step.