BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi All,

When I try to extract the data from the SharePoint using 'URL' option of 'FILENMAE' statement I'm getting an error : Unauthorized access........., but I can able to access the same data through MS Access and MS Excel.

Could any one please let me know, if you have solution to this.

Thanks in advance.
Narasimha
13 REPLIES 13
Cynthia_sas
SAS Super FREQ
Hi:
If you search on support.sas.com with the string
access SharePoint SAS

You do come up with quite a few hits. Most of them are related to the SAS Enterprise Intelligence Platform (also known as the BI Platform).

If searching on support.sas.com doesn't provide useful information, you might wish to work with Technical Support on this question. Generally speaking, SharePoint documents could be -anything- a Word doc, an HTML page, a PPT presentation, an Excel spreadsheet, a text file. The URL option of the FILENAME statement is most often used to access an HTML file and read an HTML table out of the HTML file and INTO a SAS dataset or to read a text file, as described here:
http://support.sas.com/documentation/cdl/en/lrdict/62618/HTML/default/a000223242.htm

If you are trying to publish stored process results to SharePoint, instead of to the Xythos webdav repository, you really do need to work with Tech Support.

cynthia
rab24
Calcite | Level 5

I think I am in the same boat as Narasimha. I followed the documents on connecting Sharepoint and SAS to extracts lists, but was rewarded with an Unauthorized access message to the URL. From that, I found a 2005 note http://support.sas.com/kb/15/692.html that indicates that Filename URL won’t work with the authorization issue I encountered.

Since some of the sample documentation (e.g., SAS Global Paper 390-2009) shows Filename URL working, I am wondering if this indicates that my Sharepoint install is not as basic as I thought or the samples used a program that I don’t have.  Can anyone tell me if the functionality for import direct from Sharepoint is only intended to work with Web Parts or for users with a certain level of security?

RD2
Fluorite | Level 6 RD2
Fluorite | Level 6

I use following code to download a xls file from sharepoint through proxy server

filename out "C:\temp\testout.xls"; 

proc http      

   in=in       

   out=out      

   url="http://collaboration.test.xyz.net/cnrd/cd/cdstudycode/XXX/Structure.xls

   method="get"     

      proxyhost="xxx.xx"      

            proxyusername="xxx"

   proxypassword="xxx"

      proxyport=8080; 

run; 

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

Randy,

Can you tell me how you figured out what the url is of the xls format. Whenever I attempt to figure it out, it exports out but I can't figure out the URL.

Thanks.

RD2
Fluorite | Level 6 RD2
Fluorite | Level 6

In  your  browser,  move your mouse to the correspond EXCEL file icon, right click mouse,  and select 'Copy link address'.   Then you can copy the full url from clipbord.

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

Randy;

Unfortunately I do not seem to have the same menu options. However, Thanks for the tip.

Lawrence

Bill
Quartz | Level 8

The code below works for me ...

PROC IMPORT OUT= WORK.SHAREPOINTIMPORT

DATAFILE= "\\sharepoint02\Technology\Reports\Quality\CPQ.xls"

DBMS=EXCEL REPLACE;

SHEET="AUTOX";

GETNAMES=YES;

MIXED=NO;

SCANTEXT=YES;

USEDATE=YES;

SCANTIME=YES;

RUN;

Tom
Super User Tom
Super User

Unfortunately that method will only work if you are running SAS on your Windows machine.

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

I have SAS running on my machine.

Tom
Super User Tom
Super User

Then the question with this syntax is whether your Sharepoint server allows you to view the document folder as if it was a actual network folder.  On the sites I have used you can access a document library as if it was a network folder by selecting Open in Windows Explorer from the Actions menu. See image below.

sharepoint.gif

I assume that is a feature that the site owner can turn on or off depending on the security settings that they need, but the details of how are beyond me.

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

Yea, the permissions are set by IT staff of which I have no control over.

Looking at Plan B right now.

Lawrence

Bill
Quartz | Level 8

You may not be able to "control" the settings, but you can ask to have them changed. IT is (should be) there to help us to do our jobs, not to create barriers!

Security settings can be very narrowly defined so that you only get as much rope as you need to do the specific task.  If you don't ask, you won't get!

_LB
Fluorite | Level 6 _LB
Fluorite | Level 6

Bill,

You are right, if you don't ask, you don't get.

Found a much easier solution actually. Our sharepoint allows (some users) to save as an Access 2007-10 DB with a linked table to sharepoint.

Solves the problem!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 13 replies
  • 25660 views
  • 5 likes
  • 7 in conversation