How to import an excel file into SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

How to import an excel file into SAS

how can I import an excel file into sas? I am new to SAS.


Accepted Solutions
Solution
‎10-23-2015 12:28 PM
Valued Guide
Posts: 765

Re: How to import an excel file into SAS

[ Edited ]

Hi.  Another approach is to treat the XLS file as a data set.  Use a LIBNAME statement and use the SHEET name within the XLS file as shiown in the data step   ...

 

libname x 'z:\test.xls';

 

data gas_el (keep=dt days eld delc gpd dgas);
set x.'sheet1$'n;
eld = kwh/days; delc = elec/days;
gpd = therms/days; dgas = gas/days;
label
eld = 'KWHS/DAY'
gpd = 'THERMS/DAY'
delc = 'ELEC $/DAY'
dgas = 'GAS $/DAY'
dt   = 'DATE'
days = 'DAYS USE'
;
format dt monyy. gas elec delc dgas dollar7.2 eld gpd 5.1;
run;

 

libname x clear;

 

data set GAS_EL ... 
                DAYS                   ELEC    THERMS/        GAS
Obs    DATE      USE    KWHS/DAY      $/DAY      DAY        $/DAY

   1    DEC14     34       13.6        $2.45       8.1       $4.95
  2    NOV14     29       11.2        $1.77       5.6       $3.65
  3    OCT14     32       12.6        $2.16       2.2       $2.04
  4    SEP14     30       13.1        $2.30       0.9       $1.29
  5    AUG14     28       15.4        $2.67       0.6       $1.21
  6    JUL14     33       14.8        $2.53       0.5       $1.08
  7    JUN14     27       15.0        $2.58       0.7       $1.45
  8    MAY14     32       13.3        $2.49       1.5       $2.19
  9    APR14     32       11.9        $1.76       4.4       $4.45
 10    MAR14     29       11.0        $3.11       9.9       $9.71
 11    FEB14     29       11.4        $2.67      10.3       $7.57
 12    JAN14     29       12.6        $2.87      11.9       $7.81

View solution in original post


All Replies
Super User
Posts: 5,387

Re: How to import an excel file into SAS

Search for "import excel" at support.sas.com.

Data never sleeps
Solution
‎10-23-2015 12:28 PM
Valued Guide
Posts: 765

Re: How to import an excel file into SAS

[ Edited ]

Hi.  Another approach is to treat the XLS file as a data set.  Use a LIBNAME statement and use the SHEET name within the XLS file as shiown in the data step   ...

 

libname x 'z:\test.xls';

 

data gas_el (keep=dt days eld delc gpd dgas);
set x.'sheet1$'n;
eld = kwh/days; delc = elec/days;
gpd = therms/days; dgas = gas/days;
label
eld = 'KWHS/DAY'
gpd = 'THERMS/DAY'
delc = 'ELEC $/DAY'
dgas = 'GAS $/DAY'
dt   = 'DATE'
days = 'DAYS USE'
;
format dt monyy. gas elec delc dgas dollar7.2 eld gpd 5.1;
run;

 

libname x clear;

 

data set GAS_EL ... 
                DAYS                   ELEC    THERMS/        GAS
Obs    DATE      USE    KWHS/DAY      $/DAY      DAY        $/DAY

   1    DEC14     34       13.6        $2.45       8.1       $4.95
  2    NOV14     29       11.2        $1.77       5.6       $3.65
  3    OCT14     32       12.6        $2.16       2.2       $2.04
  4    SEP14     30       13.1        $2.30       0.9       $1.29
  5    AUG14     28       15.4        $2.67       0.6       $1.21
  6    JUL14     33       14.8        $2.53       0.5       $1.08
  7    JUN14     27       15.0        $2.58       0.7       $1.45
  8    MAY14     32       13.3        $2.49       1.5       $2.19
  9    APR14     32       11.9        $1.76       4.4       $4.45
 10    MAR14     29       11.0        $3.11       9.9       $9.71
 11    FEB14     29       11.4        $2.67      10.3       $7.57
 12    JAN14     29       12.6        $2.87      11.9       $7.81

🔒 This topic is solved and locked.

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

Discussion stats
  • 2 replies
  • 255 views
  • 0 likes
  • 3 in conversation