topic Re: DS2 and libname xlsx in SAS Programming

DS2 and libname xlsx

AndersBergquist — Fri, 01 Jun 2018 06:30:36 GMT

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;

DS2 and libname xlsx

AndersBergquist — Fri, 01 Jun 2018 06:33:13 GMT

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;

Re: DS2 and libname xlsx

RW9 — Fri, 01 Jun 2018 07:35:57 GMT

http://documentation.sas.com/?docsetId=proc&docsetVersion=9.4&docsetTarget=p05tdglkbczcp1n1ijbhzakb1cet.htm&locale=en

"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;

Re: DS2 and libname xlsx

AndersBergquist — Fri, 01 Jun 2018 07:58:51 GMT

Thanks,

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

/Anders

Re: DS2 and libname xlsx

Kurt_Bremser — Fri, 01 Jun 2018 09:38:22 GMT

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

Re: DS2 and libname xlsx

Patrick — Sat, 02 Jun 2018 10:08:23 GMT

@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.

Re: DS2 and libname xlsx

AndersBergquist — Mon, 04 Jun 2018 06:55:23 GMT

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

Re: DS2 and libname xlsx

AndersBergquist — Mon, 04 Jun 2018 06:56:53 GMT

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