Learning SAS? Welcome to the exclusive online community for all SAS learners.

How to get excel sheet name is a data table name

Reply
Contributor
Posts: 40

How to get excel sheet name is a data table name

Hi friends

I have a excel file .

This excel file have different excel sheets like jan14, feb14, mar14, apr14 and  may14.

how to get excel sheet name is  a data table name .

OR

how to get excel sheet name is  a column value .

Super User
Super User
Posts: 7,392

Re: How to get excel sheet name is a data table name

Please clarify.  Do you mean you want a dataset in SAS named jan 14.  This is not possible as SAS does not allow spaces in a dataset name.  You could put it in the dataset label.  You could, in theory set a libname to your Excel file: http://www2.sas.com/proceedings/sugi31/024-31.pdf

The check out the metadata SASHELP.VTABLE where libname is your libname to Excel, to get the sheet names and put them in a column.  Its probably too much work though.  Personally, your problems all stem from using Excel as some sort of data capture, try using a data transfer capture (database) / transfer format (XML, CSV etc.).

Contributor
Posts: 40

Re: How to get excel sheet name is a data table name

Hi sir

excel sheet name are jan14,feb14,mar14 and apr14.

Super User
Super User
Posts: 7,392

Re: How to get excel sheet name is a data table name

So, just now I have created and Excel file with two tabs, jan14, feb14.  I then put this code into SAS:

libname wkrbk excel "s:\temp\rob\a.xlsx";

Then I open SASHELP.VTABLE, where libname="WRKBK" and I have two data items, memname="jan14$" and "feb14$".  So all I need to do to get sheetnames into a dataset is:

data tmp;

     set sashelp.vtable (where=(libname="WRKBK"));

run;

As for having one table per sheet, a simliar method would be:

data _null_;

     set sashelp.vtable (where=(libname="WRKBK"));

     call execute('data '||strip(compress(memname,"$"))||'; set wrkbk."'||strip(memname)||'"n; run;');  /* May need to swap the quote over, ie double around single */

run;

Just to add, make sure you: libname wkbk clear; when you are finished.

Frequent Contributor
Posts: 111

Re: How to get excel sheet name is a data table name

/*EXCEL SHEET NAMES AS DATASET NAMES IN SAS*/
/*HAVING EXCEL FILE WITH SHEET NAMES OF JAN 2014-DEC 2014, IMPORTING THESE SHEETS AND MAKE INTO A DATASET*/
/*THE DATASET NAME CONTAINS JAN_2014-DEC_2014*/
/*READING ALL EXCEL SHEETS INTO DATA LIBRARY*/
LIBNAME DATA EXCEL "D:\ANALYSIS\RK\ACTIVITY REPORT.XLSX" MIXED=YES;

/*USING SYSMAXLONG TO ASSIGN A SHEET NAMES TO MACRO VARIABLES AND N FOR COUNT OF NUMBER OF SHEETS*/
PROC SQL;
SELECT MEMNAME, COUNT(MEMNAME) INTO
  Smiley FrustratedHEET1-Smiley FrustratedHEET&SYSMAXLONG,: N
FROM DICTIONARY.TABLES
WHERE LIBNAME="DATA";
QUIT;

/*CREATING A DATASETS USING GLOBAL MACRO VAIRABLES OF SHEET NAMES*/
%MACRO IMPORTING_1;
%DO I=1 %TO &N %BY 1;
/*REMOVING THE QUOTATION MARKS AND DOLLAR($) SYMBOL FROM SHEET NAMES*/
%LET SNAME&I=%SYSFUNC(COMPRESS(&&SHEET&I,%STR($%')));
/*ASSIGNING THE UNDERSCORE(_) IF SHEET NAMES CONTAINS SPACES*/
%LET DSNAME&I=%SYSFUNC(TRANSLATE(&&SNAME&I, '_', ' '));
PROC SQL;
CREATE TABLE &&DSNAME&I. AS
SELECT DISTINCT * FROM DATA."&&SHEET&I."N;
QUIT;
%END;
%MEND IMPORTING_1;
%IMPORTING_1;

/*OR*/
/*CREATING A DATASETS USING GLOBAL MACRO VAIRABLES OF SHEET NAMES*/
%MACRO IMPORTING_2;
%DO I=1 %TO &N %BY 1;
%GLOBAL MNAME&I DNAME&I;
/*REMOVING THE QUOTATION MARKS AND DOLLAR($) SYMBOL FROM SHEET NAMES*/
%LET SNAME&I=%SYSFUNC(COMPRESS(&&SHEET&I,%STR($%')));
/*ASSIGNING THE UNDERSCORE(_) IF SHEET NAMES CONTAINS SPACES*/
%LET DNAME&I=%SYSFUNC(TRANSLATE(&&SNAME&I, '_', ' '));
DATA &&DNAME&I.;
SET DATA."&&MONTH_NM&I."N;
RUN;
%END;
%MEND IMPORTING_2;
%IMPORTING_2;

Ask a Question
Discussion stats
  • 4 replies
  • 1045 views
  • 0 likes
  • 3 in conversation