DS2 and libname xlsx

[AndersBergquist]

Hello,

Can I got a confirmation that proc ds2 does can not access an libname xlsx?

This code will not work.

libname mydata xlsx 'myxlfile.xlsx';

run;

proc ds2;

   data work.mydata;

        set mydata.xlssheet;

   enddata;

run;quit;

[AndersBergquist]

Hello,

Can I got a confirmation that proc ds2 does can not access an libname xlsx?

This code will not work.

libname mydata xlsx 'myxlfile.xlsx';
run;

proc ds2;
       data work.mydata;
        set mydata.xlssheet;
   enddata;
run;quit;

[Kurt_Bremser]

When something "does not work", ALWAYS post the log.

[AndersBergquist]

Code;

libname myxl xlsx "C:\Dokument\Befolkningspronos\Manuell_justering\projektParametrar.xlsx";
run;
data flyttrikser_data;
	set myxl.flyttrisker;
run;
proc ds2;
	data flyttrisker_ds2;
		method run();
			set myxl.flyttrisker;
		end;
	enddata;

run;quit;

proc ds2;
	data _null_;
		method run();
			sqlexec('select * into work.flyttrikser_fsql from myxl.flyttrisker');
		end;
	enddata ;
run;quit;

log:

30         libname myxl xlsx "C:\Dokument\Befolkningspronos\Manuell_justering\projektParametrar.xlsx";
NOTE: Libref MYXL was successfully assigned as follows: 
      Engine:        XLSX 
      Physical Name: C:\Dokument\Befolkningspronos\Manuell_justering\projektParametrar.xlsx
31         run;
32         data flyttrikser_data;
33         	set myxl.flyttrisker;
34         run;

NOTE: The import data set has 12928 observations and 8 variables.
NOTE: There were 12928 observations read from the data set MYXL.flyttrisker.
NOTE: The data set WORK.FLYTTRIKSER_DATA has 12928 observations and 8 variables.
NOTE: DATA statement used (Total process time):
      real time           1.73 seconds
      cpu time            1.68 seconds
      

35         proc ds2;
36         	data flyttrisker_ds2;
37         		method run();
38         			set myxl.flyttrisker;
39         		end;
40         	enddata;
41         
42         run;
42       !     quit;
ERROR: Compilation error.
ERROR: BASE driver, schema name MYXL was not found for this connection
ERROR: Table "MYXL.FLYTTRISKER" does not exist or cannot be accessed
ERROR: Line 38: Unable to prepare SELECT statement for table flyttrisker (rc=0x80fff802U).
NOTE: PROC DS2 has set option NOEXEC and will continue to prepare statements.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE DS2 used (Total process time):
      real time           1.40 seconds
      cpu time            0.34 seconds
      
43         


44         proc ds2;
45         	data _null_;
46         		method run();
47         			sqlexec('select * into work.flyttrikser_fsql from myxl.flyttrisker');
48         		end;
49         	enddata ;
50         run;
50       !     quit;
ERROR: BASE driver, schema name MYXL was not found for this connection
ERROR: Table "MYXL.FLYTTRISKER" does not exist or cannot be accessed
ERROR: Failed to prepare SQL statement: select * into work.flyttrikser_fsql from myxl.flyttrisker
NOTE: Execution succeeded. No rows affected.

NOTE: PROCEDURE DS2 used (Total process time):
      real time           0.12 seconds
2                                                          The SAS System                                 08:38 Monday, June 4, 2018

      cpu time            0.10 seconds
      

My question: Is the result by design or have I done an error?

/Anders

[RW9]

http://documentation.sas.com/?docsetId=proc&docsetVersion=9.4&docsetTarget=p05tdglkbczcp1n1ijbhzakb1...

"When you use PROC DS2, you can submit DS2 language statements to SAS servers and DBMS data sources that are available with SAS 9.4 SAS/ACCESS engines. In addition, beginning with SAS 9.4M5, if you have SAS Viya, you can submit DS2 language statements to the CAS server." - keyword here is the SAS/ACCESS engines.  

Not sure why you would want to read in an Excel file (yep could have stopped there) in DS2 anyways, a simple change to your code should work fine:

libname mydata xlsx 'myxlfile.xlsx';

proc copy in=mydata out=work;
run;

libname mydata clear;

proc ds2;
  ...
run;quit;

[AndersBergquist]

Thanks,

I have write a demographic prognos program and use Excel as interface to data which I had to change.

/Anders

[Patrick]

---

@AndersBergquist wrote:

Thanks,

I have write a demographic prognos program and use Excel as interface to data which I had to change.

/Anders

---

For using Excel as an interface to SAS tables: The SAS Add-In to Microsoft Office (SAS AMO) could make this really simple for you.

SAS AMO comes with offerings like SAS Office Analytics.

[AndersBergquist]

I know about AMO but it works not on a SAS Workstation licens.

I consider useing AMO on an other set up with OA-server.

/Anders