BookmarkSubscribeRSS Feed
AndersBergquist
Quartz | Level 8

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;

7 REPLIES 7
AndersBergquist
Quartz | Level 8

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
Quartz | Level 8

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
Diamond | Level 26 RW9
Diamond | Level 26

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
Quartz | Level 8

Thanks,

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

/Anders

Patrick
Opal | Level 21

@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
Quartz | Level 8

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

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1365 views
  • 0 likes
  • 4 in conversation