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

Hi all,

I have a excel file of insurance details, in this there will be various sheets.
2015 data contains the sheets of 2015-01-01-04-31, 2015-04-31-06-30, 2015-06-30-09-30.

Can i read this sheets instead mentioning full text of sheet name by only 2015 year.
I followed below code for import but unable to read the data, Any help would be greatly appreciated.


PROC IMPORT OUT=DATA_2015
DATAFILE="D:\Insurance details.xlsx"
DBMS=EXCEL REPLACE;
SHEET="2015*.";
GETNAMES=YES;
MIXED=YES;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ravikumarkummari
Quartz | Level 8

Solved:

LIBNAME DATA "D:\Insurance details.xlsx" MIXED=YES;

/*CREATING MACROS FOR NUMBER OF SHEETS EXISTED IN EXCEL FILE CONTAINS 2015 YEAR*/
/*MACRO N FOR NUMBER OF SHEETS THAT CONTAINS SHEET NAME 2015*/
PROC SQL;
SELECT COUNT(*) AS ROWS, MEMNAME INTO: N,
:SHEET1-:SHEET&SYSMAXLONG
FROM DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)="DATA" AND MEMNAME CONTAINS "2015";
QUIT;

/*READ THE ALL SHEETS CONTAINS 2015 YEAR*/
%MACRO YEAR_2015;
DATA INSDATA_2015;

SET %DO I=1 %TO &N; DATA."&&SHEET&I."N
%END;
;
RUN;
%MEND YEAR_2015;

%YEAR_2015;

View solution in original post

3 REPLIES 3
error_prone
Barite | Level 11

Yet another problem solely existing because someone decided that excel is a proper file format for storing data 😞

 

If you can't get rid of the excel files:

  • use libname with xlsx engine and sashelp.vtable to get the sheet names
  • important: set global option validmemname = any
  • now you have all sheets in a sas library and don't need proc import at all
  • you will, of course, have to standardize variable lengths to work with the data
Ksharp
Super User
Use Libname + xlsx engine to copy all the tables into SAS, 
and then pick up 2015 tables.

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


Ravikumarkummari
Quartz | Level 8

Solved:

LIBNAME DATA "D:\Insurance details.xlsx" MIXED=YES;

/*CREATING MACROS FOR NUMBER OF SHEETS EXISTED IN EXCEL FILE CONTAINS 2015 YEAR*/
/*MACRO N FOR NUMBER OF SHEETS THAT CONTAINS SHEET NAME 2015*/
PROC SQL;
SELECT COUNT(*) AS ROWS, MEMNAME INTO: N,
:SHEET1-:SHEET&SYSMAXLONG
FROM DICTIONARY.TABLES
WHERE UPCASE(LIBNAME)="DATA" AND MEMNAME CONTAINS "2015";
QUIT;

/*READ THE ALL SHEETS CONTAINS 2015 YEAR*/
%MACRO YEAR_2015;
DATA INSDATA_2015;

SET %DO I=1 %TO &N; DATA."&&SHEET&I."N
%END;
;
RUN;
%MEND YEAR_2015;

%YEAR_2015;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 3 replies
  • 1314 views
  • 3 likes
  • 3 in conversation