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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.