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;
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;
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 + 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;
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;
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.
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.
Ready to level-up your skills? Choose your own adventure.