Home Forums Main Forums SAS Forum Automatically read data from different Excel sheets into SAS datasets

  • Automatically read data from different Excel sheets into SAS datasets

     Justin updated 7 months, 1 week ago 1 Member · 1 Post
  • Justin

    Administrator
    February 7, 2021 at 5:04 pm

    Task:

    If a Excel file contains numerous different sheets, the sheet names may have blanks and other invalid SAS symbols: (e.g., sales 2007-2012, revenue $2011). Can we read the data from different sheets into different SAS datasets? Can we do it automatically without change the sheet names manually?

    options mprint source2 symbolgen; 
    libname SALES Excel "file path\sales.xls" ver=2002;
    filename SALES "file path\sales.xls" ver=2002;

    proc sql;
    create table Excel_Sheets(keep=libname memname) as
    select *
    from sashelp.VTABLE
    where upcase(libname)='SALES';
    quit;

    data AAA;
    length Memname In_Name Out_Name $30;
    set Excel_Sheets;
    In_Name=scan(memname, 1, '.');
    Out_Name=compress(In_Name, '- / # $ *~');
    run;

    %macro Read;
    proc sql;
    select count(*) into : N trimmed
    from AAA;
    %let N=&N;

    select In_Name, Out_Name into : In1-:In&N, :OUT1- :OUT&N
    from AAA;
    quit;

    %local J;
    %do J=1 %to &N;

    data &&Out&J;
    set sales."&&In&J"n ;
    run;

    proc import datafile=Sales out= &&Out&J DBMS=Excel replace;
    sheet = "&&In&J.$"n;
    getnames = yes;
    mixed = yes;
    run;

    %end;
    %mend;
    %Read;

    libname sales clear;

    Note:
    The key challenge in this macro is to use Libname Excel engine and Dictionary or SASHelp. We need them to read the sheet names into a SAS data set, then use it to create macro vars.

Log in to reply.

Original Post
0 of 0 posts June 2018
Now