Excel Import to SAS

Reply
Frequent Contributor
Posts: 138

Excel Import to SAS

Hi, I need to import 4 x tabs and have them in 4 separate tables within within SAS

Excel Worksheet Location -

Mortgage finance\a&tp\DM\CRDM Flow Model

Excel Name worksheet name–

CRDM Flow Model

Tab Names

BO data – Allocations

BO data – Completions

Hedging Assumptions 1

Hedging Assumptions 2

Can anyine help with the code to allow this to work please?

Many Thanks

Frequent Contributor
Posts: 138

Re: Excel Import to SAS

Any ideas please?

Esteemed Advisor
Esteemed Advisor
Posts: 7,217

Re: Excel Import to SAS

You have some ideas from the above.  My counsel is, always, this.  Don't use Excel.  There are plenty of issues with using that format and software as it was never built as a data capture tool, database, or anything else you can think of.  If you are unfortunate and have data in Excel, then first stage would be to get the data out of that format and into a proper data structure.  Save each tab to a CSV file - if that's too much effort, write a small VBA macro to do it, e.g. for each sheet in worksheets.sheets ...

Once you have data in a data structure, write and import program, a datastep with a an infile statement, and specify the format, informat, length etc. of each data item to read in.  In this way you are telling SAS what data to get in, and if it finds strange artifacts, which as you are using Excel you will, then it will highlight these and warn you.  You could use proc import, but that merely guesses what you want to do, however you can use it to generate the skelleton of the program - check the log after you run an import and it will show you the datastep needed to read in the data.  You can then modify that.

Contributor
Posts: 37

Re: Excel Import to SAS

A libname statement would do ,

libname tabs 'Mortgage finance\a&tp\DM\CRDM Flow Model\CRDM Flow Model.xls';

once you execute the above libname , you can see separate datasets for each sheet at the explorer window with a dollar sign.

the dataset names will be like

BO data – Allocations$

BO data – Completions$

Hedging Assumptions 1$

Hedging Assumptions 2$

make sure the tab names are SAS compatible

Thanks

Contributor
Posts: 65

Re: Excel Import to SAS

One more approach :

proc import datafile = "path\abc.xls"

                 out =datasetname

                 dbms = excel

                 replace;

sheet = " ";

run;

Sheetname should not exceed 31 characters.

%macro Import(path = ,outputdataset = , sheetname = );

proc import datafile = "&path" out = &outputdataset dbms=excel replace;

sheet = "&sheetname";

run;

%mend;

%import(path= Specify the file path ,outputdataset = tab1 ,sheetname =BO data – Allocations)

%import(path= Specify the file path,outputdataset = tab2 ,sheetname =BO data – Completions)

%import(path= Specify the file path,outputdataset = tab3 ,sheetname =Hedging Assumptions 1)

%import(path= Specify the file path,outputdataset = tab4 ,sheetname =Hedging Assumptions 2)

Frequent Contributor
Posts: 138

Re: Excel Import to SAS

Thank you so much for your help, I will look to test this later today. Much appreciated.

Grand Advisor
Posts: 9,578

Re: Excel Import to SAS

Libname + call execute()

Code: Program

libname x xlsx '/folders/myfolders/x.xlsx';


data x;
set sashelp.vmember(keep=libname memname where=(libname='X')) end=last;
if _n_ eq 1 then call execute('proc sql;');
call execute(catt('create table want',_n_,' as select * from x.',nliteral(memname),';' ));
if last then call execute(';quit;');
run;


Log: Program

Notes (10)

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

53 

54 libname x xlsx '/folders/myfolders/x.xlsx';

NOTE: Libref X was successfully assigned as follows:

  Engine: XLSX

  Physical Name: /folders/myfolders/x.xlsx

55 

56 

57 data x;

58 set sashelp.vmember(keep=libname memname where=(libname='X')) end=last;

59 if _n_ eq 1 then call execute('proc sql;');

60 call execute(catt('create table want',_n_,' as select * from x.',nliteral(memname),';' ));

61 if last then call execute(';quit;');

62 run;

NOTE: There were 2 observations read from the data set SASHELP.VMEMBER.

  WHERE libname='X';

NOTE: The data set WORK.X has 2 observations and 2 variables.

NOTE: DATA statement used (Total process time):

  real time 0.04 seconds

  cpu time 0.04 seconds

  

NOTE: CALL EXECUTE generated line.

1 + proc sql;

2 + create table want1 as select * from x.SHEET1;

NOTE: The import data set has 6 observations and 4 variables.

NOTE: Table WORK.WANT1 created, with 6 rows and 4 columns.

3 + create table want2 as select * from x."TABLE 1 - DATA SET WORK.HAVE"N;

NOTE: The import data set has 6 observations and 4 variables.

NOTE: Table WORK.WANT2 created, with 6 rows and 4 columns.

4 + ;quit;

NOTE: PROCEDURE SQL used (Total process time):

  real time 0.10 seconds

  cpu time 0.09 seconds

  

63 

64 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

74 

XIa Keshan

Ask a Question
Discussion stats
  • 6 replies
  • 343 views
  • 2 likes
  • 5 in conversation