DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 111
Accepted Solution

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

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;

 


Accepted Solutions
Solution
‎09-19-2016 07:46 AM
Frequent Contributor
Posts: 111

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

[ Edited ]

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,
Smiley FrustratedHEET1-Smiley FrustratedHEET&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


All Replies
Contributor
Posts: 33

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

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
Super User
Posts: 9,671

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

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; 


Solution
‎09-19-2016 07:46 AM
Frequent Contributor
Posts: 111

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

[ Edited ]

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,
Smiley FrustratedHEET1-Smiley FrustratedHEET&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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 287 views
  • 3 likes
  • 3 in conversation