topic Re: Extracting excel sheet name in sas in SAS Programming

Extracting excel sheet name in sas

varshabansal — Tue, 17 Oct 2023 11:23:24 GMT

Hi everyone!

Please help me, I want to extracting all sheet name from my excel file and then want to use those sheet name as table names in my script dynamically.

Eg- sheet1. sheet2 are my sheet name

and sheet1 and sheet2 will be also my tablename

Re: Extracting excel sheet name in sas

ErikLund_Jensen — Tue, 17 Oct 2023 11:41:04 GMT

Hi @varshabansal

libname bjlist xlsx "/mnt/dwhadmin_prod/Work/pensiontjek_05-03-2023.xlsx";

proc contents data=bjlist._all_ noprint out=x;
run;

proc sql noprint;
   select distinct memname into :memlist separated by ' ' from x;
quit;
%put &=memlist;

The code above will gve a macro variable containing a list of sheet names.

Re: Extracting excel sheet name in sas

Ksharp — Tue, 17 Oct 2023 11:48:08 GMT

%let excel= c:\temp\date.xlsx  ;   *path for inputing excel file;



options validvarname=any validmemname=extend;
libname x xlsx "&excel.";
data _null_;
 set sashelp.vtable(keep=memname libname where=(libname='X'));
call execute(catt('proc import datafile="&excel." out=',compress(memname,,'kda'),' dbms=xlsx replace;sheet="',memname,'";run;'));
run;

Re: Extracting excel sheet name in sas

varshabansal — Tue, 17 Oct 2023 12:00:08 GMT

/**Assign a libname for excel sheet*/
libname inexcel xlsx '/home/viadmin/casuser/mapping.xlsx';

PROC CONTENTS DATA=inexcel._ALL_ noprint
OUT=Sheets (KEEP=Memname);
RUN;

PROC SORT DATA=Sheets NODUPLICATES;
BY Memname;
RUN;

DATA Sheets;
SET Sheets;
ID=_N_;
RUN;

DATA _NULL_;
IF 0 THEN SET Sheets NOBS=X;
CALL SYMPUT('Recount', X);
STOP;
RUN;

%macro Excel;
%DO i = 1 %TO &Recount;
PROC SQL ;
create table sheet&i. as
SELECT Memname INTO :Name
FROM Sheets WHERE ID = &i;
quit;
%end;
%mend;

%excel;

%dataload(target_table ='sheet&i.');

I have done with, but i want that target_table automatically will be call %dataload(target_table ='sheet&i.');

as sheet 1,
Like this-
%dataload(target_table ='sheet1.');
%dataload(target_table ='sheet2.');

Re: Extracting excel sheet name in sas

Tom — Tue, 17 Oct 2023 12:45:04 GMT

If it is an XLSX file you can read the XML file where the list of sheets is stored from inside of the XLSX file.

See this old thread: https://communities.sas.com/t5/SAS-Programming/How-to-import-second-sheet-from-Excel-workbook-in-to-SAS-without/td-p/669265

Or even simpler use the code in this other thread:

https://communities.sas.com/t5/SAS-Programming/Multiple-sheet-names/m-p/872092/highlight/true#M344531

data worksheets;
  infile "c:\downloads\book1.xlsx" zip member='xl/workbook.xml' recfm=n dsd dlm=' ';
  sheet_number+1;
  input @'<sheet name=' sheet_name :$32. @@;
run;