Reading an Excel file with multiple sheets and performing descriptive statistics

[Sushant1]

Hi All

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 for all rows. Each sheet has 18262 rows. I need to read all the sheets together in SAS and print it, so that I can do descriptive statistics on Column 5, 9 and 10. Besides I need to draw their occurrence/frequency distributions as well in form of graph. So basically it makes 50 replications in those 50 worksheets. I ran the below mentioned code for single file.

proc import out = data Datafile = " C:\Personal\Data_2016\data_new.xlsx"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.

Any reply will be appreciated.

Regards
Sushant

[ChrisNZ]

Like this?

%macro loop; 
%local i;
%do i=1 to 50;
proc import out      = DATA_XL_&i.
            datafile = " C:\Personal\Data_2016\data_new.xlsx"
            dbms     = xlsx 
            replace; 
  sheet = "AB&i" 
  getnames = yes; 
run; 
%end;
%mend;
%loop;

data ALL;
  set DATA_XL_: (keep=COL1 COL5 COL9 COL10);
run;

proc univariate data=ALL; 
  by COL1;
run;

Replace column names obviously.

[rogerjdeangelis]

Hi TeamAnother possible solution?* You need full SAS for this;                                              
                                                                           
* wish sas would deprecate 'proc import/export' and                        
beef up the excel engines, they are potentially                            
more powerfull, especially when used with passthru;                        
                                                                           
HAVE                                                                       
                                                                           
  Excel sheets  (from SASHELP.CARS)                                        
                                                                           
XLS.'_asia$'n.                                                             
XLS.'_europe$'n.                                                           
XLS.'_usa$'n.                                                              
                                                                           
WANT (create single dataset SASHELP.CARS)                                  
                                                                           
Middle Observation(214 ) of WORK.CARS - Total Obs 428                      
                                                                           
                                                                           
 -- CHARACTER --                                                           
MAKE                 C    13      Kia                                      
MODEL                C    40       Sedona LX                               
TYPE                 C    8       Sedan                                    
ORIGIN               C    6       Asia                                     
DRIVETRAIN           C    5       Front                                    
                                                                           
                                                                           
 -- NUMERIC --                                                             
MSRP                 N    8       20615                                    
INVOICE              N    8       19400                                    
ENGINESIZE           N    8       3.5                                      
CYLINDERS            N    8       6                                        
HORSEPOWER           N    8       195                                      
MPG_CITY             N    8       16                                       
MPG_HIGHWAY          N    8       22                                       
WEIGHT               N    8       4802                                     
WHEELBASE            N    8       115                                      
LENGTH               N    8       194                                      
                                                                           
                                                                           
* below I create a excel workbook with                                     
                                                                           
* create 3 sheets ;                                                        
                                                                           
* Humpty Dumpty fell off the wall;                                         
%utlfkil(d:/xls/origin.xlsx);                                              
libname xls "d:/xls/origin.xlsx";                                          
data xls._asia xls._usa xls._europe;                                       
    set  sashelp.cars;                                                     
    select (origin);                                                       
       when ( 'Asia'  ) output xls._Asia   ;                               
       when ( 'Europe') output xls._Europe ;                               
       when ( 'USA'   ) output xls._USA    ;                               
       /* leave off otherwise to force an error */                         
    end;                                                                   
;run;quit;                                                                 
                                                                           
/*                                                                         
NOTE: There were 428 observations read from the data set SASHELP.CARS.     
NOTE: The data set XLS._asia has 158 observations and 15 variables.        
NOTE: The data set XLS._usa has 147 observations and 15 variables.         
NOTE: The data set XLS._europe has 123 observations and 15 variables.      
*/                                                                         
                                                                           
* Humpty Dumpty was put back together again;                               
data cars;                                                                 
  set xls._:;                                                              
run;quit;                                                                  
                                                                           
/*                                                                         
NOTE: There were 158 observations read from the data set XLS.'_asia$'n.    
NOTE: There were 123 observations read from the data set XLS.'_europe$'n.  
NOTE: There were 147 observations read from the data set XLS.'_usa$'n.     
NOTE: The data set WORK.CARS has 428 observations and 15 variables.        
*/                                                                         
                                                                           
                                                                           
proc means data=cars median;                                               
;run;quit;                                                                 
                                                                           
/*                                                                         
The MEANS Procedure                                                        
                                                                           
Variable       Label                Median                                 
------------------------------------------                                 
MSRP           MSRP               27635.00                                 
INVOICE        INVOICE            25294.50                                 
ENGINESIZE     ENGINESIZE        3.0000000                                 
CYLINDERS      CYLINDERS         6.0000000                                 
HORSEPOWER     HORSEPOWER      210.0000000                                 
MPG_CITY       MPG_CITY         19.0000000                                 
MPG_HIGHWAY    MPG_HIGHWAY      26.0000000                                 
WEIGHT         WEIGHT              3474.50                                 
WHEELBASE      WHEELBASE       107.0000000                                 
LENGTH         LENGTH          187.0000000                                 
------------------------------------------                                 
*/