topic Re: How to connect with the excel using "libname" statement..? in SAS Programming

How to connect with the excel using "libname" statement..?

varmalh — Wed, 25 Sep 2019 02:02:44 GMT

Hello,

Can someone please advise me that how i can connect with the excel using "Libname" statement as tried this in my crome and internet explores browser but nothing happen.

than

libname y xlsx "C:\Users\Hp\Desktop\Cost_Basis_Project\Market_Price_Sheet.xlsx";

proc contents data=x._all_ nods;

run;
proc print data=x.market;
run;

ks.

Re: How to connect with the excel using "libname" statement..?

SASKiwi — Wed, 25 Sep 2019 02:05:56 GMT

Please post your SAS log.

Re: How to connect with the excel using "libname" statement..?

varmalh — Wed, 25 Sep 2019 02:19:04 GMT

here i just run the libname statement ....

Re: How to connect with the excel using "libname" statement..?

Patrick — Wed, 25 Sep 2019 02:29:45 GMT

@varmalh

Looks like the Libname works. What does the Proc Contents return?

....and looking at the code you've posted:

Wouldn't you need to use libref Y instead of X?

proc contents data=Y._all_ nods;

run;quit;

Re: How to connect with the excel using "libname" statement..?

varmalh — Wed, 25 Sep 2019 02:33:50 GMT

@Patrick i don't think libname worked as u can see there is no sheet mention on the left hand side of libraries..!!

please also see the result of Proc content..!

Re: How to connect with the excel using "libname" statement..?

varmalh — Wed, 25 Sep 2019 02:36:42 GMT

@Patrick i used libreg Y and X both just to be sure but nothing positive .....!!

Re: How to connect with the excel using "libname" statement..?

Patrick — Wed, 25 Sep 2019 02:44:33 GMT

The libname doesn't return an error so the file appears to exist.

Make sure you don't have the file opened via Excel when you run your code (also I believe the libname would throw an error in such a case).

How does the data in your Excel actually look like. Does data start in cell A1 or is it in a different structure?

Can you share your Excel with us (as an attachment here)? Many people won't open Excels - but some do...

Re: How to connect with the excel using "libname" statement..?

SASKiwi — Wed, 25 Sep 2019 04:27:35 GMT

Please try the EXCEL engine on your LIBNAME rather than XLSX.

Also don't forget to refresh your server list after applying the LIBNAME. Tables / tabs won't show until you do this.

Re: How to connect with the excel using "libname" statement..?

varmalh — Wed, 25 Sep 2019 23:54:20 GMT

hi @SASKiwi @Patrick ,

i noticed one thing that i could not able to see my system folder i don't know why....!

Re: How to connect with the excel using "libname" statement..?

SASKiwi — Thu, 26 Sep 2019 01:38:09 GMT

@varmalh - Run PROC SETINIT and PROC PRODUCT_STATUS to confirm if you have SAS/ACCESS to PC Files - Excel LIBNAMES of any form won't work if you don't have the software:

proc setinit;
run;

proc product_status;
run;

Re: How to connect with the excel using "libname" statement..?

Tom — Thu, 26 Sep 2019 14:30:04 GMT

You appear to be connecting to SAS using the SAS/Studio application. Make sure that the XLSX file you want to read is on the machine that is actually running SAS, it might a different machine (physically or logically) than the machine where you are running the browser that is connecting to SAS/Studio.

Note that if you issue that LIBNAME statement and the file does not exist then SAS will create an empty file so you can copy data into it. That would explain why it appears to be empty.