CODEWITHSUNDEEP
sas
Sam
Sam

Reputation: 13

Reading excel file with multiple sheets in SAS 9.4

I have an excel file with 50 worksheets AB0 to AB49. Each worksheet consists of 14 columns, with column names same in all the sheets. The first column in every sheet varies from a number from 1 in sheet 1 to 50 in sheet 50. Each sheet has 18262 rows. I need to read all the sheets together in SAS and print it. I need to do descriptive statistics on Column 5, 9 and 10. Besides I need to draw their occurrence distributions as well in form of graph. 

proc import DATAFILE = " C:\Personal\Data_2016\data_new.xlsx"
            OUT = data 
            DBMS = xlsx 
            REPLACE;
    SHEET = "AB0"
    GETNAMES = Yes;
run;

proc univariate;   
run;

In this code i am getting Moments, Basic Statistical Measures, test for location; Mu = 0, Quantiles, Extreme Observations as an output for all fourteen columns, where as I want output from all 50 sheets together ( 18262 (in one sheet) * 50 values in each column) for column 5, 9 and 10 only.

Upvotes: 1

Views: 7314

Answers (2)

Tom
Tom

Reputation: 51566

You should be able to combine them into a single dataset. Instead of using PROC IMPORT use the XLSX libname engine.

 libname in xlsx  'C:\Personal\Data_2016\data_new.xlsx' ;
 data all_data;
   set in.AB0 - in.AB49 ;
 run;

You will need to have SAS/Access to PC Files licensed. But you do not need either Microsoft Windows or Excel software.

Upvotes: 7

Sean
Sean

Reputation: 1120

If you provide more details on what summary statistics you want we can be of more help to you, but the import is straightforward enough:

%macro import_loop;
    %do i = 0 %to 49;
        proc import datafile = "C:\Personal\Data_2016\data_new.xlsx"
            out = import_sheet&i.
            dbms = xlsx replace;
            getnames = yes;
            sheet = "Sheet&i.";
        run;
    %end;
%mend import_loop;

%import_loop;

data stack;
    set import_sheet:;
run;

Now you have all 50 datasets stacked and ready to work with.

Upvotes: 0

Related Questions