topic Re: how to import excel file by mentioning year in place of sheet name in SAS Programming

how to import excel file by mentioning year in place of sheet name

Ravikumarkummari — Mon, 19 Sep 2016 07:55:05 GMT

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;

Re: how to import excel file by mentioning year in place of sheet name

error_prone — Mon, 19 Sep 2016 08:15:38 GMT

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

Re: how to import excel file by mentioning year in place of sheet name

Ksharp — Mon, 19 Sep 2016 09:20:46 GMT

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;

Re: how to import excel file by mentioning year in place of sheet name

Ravikumarkummari — Mon, 19 Sep 2016 11:47:37 GMT

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;