topic Re: Excel engine to import excel file into sas data set in SAS Programming

Excel engine to import excel file into sas data set

Ronein — Mon, 17 Aug 2020 12:28:14 GMT

Hello

I know how to use excel engine to export sas data set into excel file.

Let's say that I exported a sas data set into excel file.

Let's say that a few days later I want to import this excel file into sas using excel engine.

What is the way to do it please?

I get error because the code of import using excel engine is not correct.

/*Excel engine to export sas data set into excel file*/
/*Export to excel file called Example that included one sheet called cars*/
libname RRR xlsx "/path/Example.xlsx";
data RRR.cars;
set sashelp.cars ;
run;
 

/*Excel engine to import excel file into sas data set*/
/****???????????How should I do it please???????*/
data cars;
set RRR.Example;
run;

Re: Excel engine to import excel file into sas data set

PaigeMiller — Mon, 17 Aug 2020 12:36:06 GMT

If you look at the Excel file that was created, you will see you have a sheet named CARS.

Therefore, when you want to import from that Excel file, you have to import the sheet named ______ (you fill in the blank).

So, your SAS command has to look for RRR.__________ (you fill in the blank)

Re: Excel engine to import excel file into sas data set

Kurt_Bremser — Mon, 17 Aug 2020 12:35:41 GMT

You use a different name (Example) for import than you used for export (cars). Otherwise, your code will work:

 73         libname rrr xlsx "/folders/myfolders/ronein.xlsx";
 NOTE: Libref RRR was successfully assigned as follows: 
       Engine:        XLSX 
       Physical Name: /folders/myfolders/ronein.xlsx
 74         
 75         data rrr.cars;
 76         set sashelp.cars;
 77         run;
 
 NOTE: There were 428 observations read from the data set SASHELP.CARS.
 NOTE: The data set RRR.cars has 428 observations and 15 variables.
 NOTE: The export data set has 428 observations and 15 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.05 seconds
       cpu time            0.04 seconds
       
 
 78         
 79         
 80         data cars;
 81         set rrr.cars;
 82         run;
 
 NOTE: The import data set has 428 observations and 15 variables.
 NOTE: There were 428 observations read from the data set RRR.cars.
 NOTE: The data set WORK.CARS has 428 observations and 15 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.08 seconds
       cpu time            0.06 seconds

Re: Excel engine to import excel file into sas data set

PaigeMiller — Mon, 17 Aug 2020 12:37:25 GMT

Which brings up the question ... why are you saving this data in Excel and then reading it from Excel later?

Why not just save it as a permanent SAS data set? Much easier, IMHO.

Re: Excel engine to import excel file into sas data set

Ronein — Mon, 17 Aug 2020 13:57:17 GMT

So as you said I need to write the name of sheet and not the name of the file??

So How does SAS know to import this file and not another file?

Re: Excel engine to import excel file into sas data set

Ronein — Mon, 17 Aug 2020 13:58:17 GMT

It is just to illustrate the situation of import excel file using excel engine

Re: Excel engine to import excel file into sas data set

Tom — Mon, 17 Aug 2020 14:02:53 GMT

@Ronein wrote:

So as you said I need to write the name of sheet and not the name of the file??

So How does SAS know to import this file and not another file?

The name of the file is given in the LIBNAME statement.

Re: Excel engine to import excel file into sas data set

PaigeMiller — Mon, 17 Aug 2020 14:11:05 GMT

@Ronein wrote:

So as you said I need to write the name of sheet and not the name of the file??

So How does SAS know to import this file and not another file?

The LIBNAME points to the Excel file. The data set name (for example, RRR.CARS, the data set name is CARS) points to the tab.