https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/278492#M311120 <PRE><CODE class=" language-sas">Hi Team<BR /><BR />Another possible solution?<BR /><BR />* 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 ------------------------------------------ */ </CODE></PRE> Sun, 19 Jun 2016 17:08:10 GMT rogerjdeangelis 2016-06-19T17:08:10Z https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/277054#M311117 <P><SPAN>Hi All </SPAN><BR /><BR /><SPAN>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. </SPAN><BR /><BR /><SPAN>proc import out = data Datafile = " C:\Personal\Data_2016\data_</SPAN><SPAN>new.xlsx"DBMS = xlsx REPLACE; </SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; SHEET = "AB0" </SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; GETNAMES = Yes; </SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; RUN; </SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; proc univariate; </SPAN><BR /><BR /><SPAN>&nbsp; &nbsp; run; </SPAN><BR /><BR /><SPAN>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. </SPAN><BR /><BR /><SPAN>Any reply will be appreciated. </SPAN><BR /><BR /><SPAN>Regards </SPAN><BR /><SPAN>Sushant </SPAN></P> Mon, 13 Jun 2016 21:42:58 GMT https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/277054#M311117 Sushant1 2016-06-13T21:42:58Z https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/277093#M311118 <P>Like this?</P> <P>&nbsp;</P> <PRE><CODE class=" language-sas"> %macro loop; %local i; %do i=1 to 50; proc import out = DATA_XL_&amp;i. datafile = " C:\Personal\Data_2016\data_new.xlsx" dbms = xlsx replace; sheet = "AB&amp;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; </CODE></PRE> <P>&nbsp;</P> <P>Replace column names obviously.</P> Tue, 14 Jun 2016 02:38:00 GMT https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/277093#M311118 ChrisNZ 2016-06-14T02:38:00Z https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/278491#M311119 Sun, 19 Jun 2016 17:07:16 GMT https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/278491#M311119 rogerjdeangelis 2016-06-19T17:07:16Z https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/278492#M311120 <PRE><CODE class=" language-sas">Hi Team<BR /><BR />Another possible solution?<BR /><BR />* 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 ------------------------------------------ */ </CODE></PRE> Sun, 19 Jun 2016 17:08:10 GMT https://communities.sas.com/t5/SAS-Programming/Reading-an-Excel-file-with-multiple-sheets-and-performing/m-p/278492#M311120 rogerjdeangelis 2016-06-19T17:08:10Z