extract website list table using PROC HTTP

[peterluan]

Usually, we click on ‘LIST’ tab on a website then ‘Export to Excel’ and save as xlsx file. This will give us the right report, but it’s a manual process. Can we automate this process through PROC HTTP?

I have tried the following. It will run, but it's does not give me the right table:

%LET RootFileDirectory = C:\Users\xxxx\prod\data;
FILENAME sp_tprm  "&RootFileDirectory\sp_tprm.json";/*  sp_tprm */
%LET RESTBaseURL=http://xxxxxx.net/sites/yyyyy-038/_api;
PROC HTTP   
    URL= "&RESTBaseURL./lists/getbytitle('Category-Managed Third Party Request')/items?$top=3000"
 OUT=sp_tprm
   AUTH_NTLM METHOD="GET";
   headers 'accept' = 'application/json;odata=verbose';
RUN;

[ChrisHemedinger]

Hi @peterluan -

It depends on the website and whether there is an API or direct link to get to the data. If you have specific example/site we could explore options for you.

Check out this webinar (with code and slides provided) for general techniques that might help.

Chris

[peterluan]

Thanks for the information. I still need to investigate what you suggested.

I have captured the “owssvr.iqy” file ( when you click the ‘export to Excel’) and copy the URL in it.

Then  ran:

FILENAME sp_tprm  "C:\Users\u407770\prod\data\sp_tprm.XML";

PROC HTTP   

     URL= 'http://xxxxxx.xxxxx.xxxxx.net/sites/corprisk-opsrisk-038/_vti_bin/owssvr.dll?XMLDATA=1&List={ABF6A9A...'

     OUT=sp_tprm

     AUTH_NTLM

     METHOD="GET";

RUN;

“sp_tprm.XML” looks good.

Stokke, Delayne H.<Delayne.Stokke@wellsfargo.com> helped me with reading the xml file using the SAS XML mapper to come up with xml map and xml tables. However, he found transcode errors, and was able to read the whole file using SAS 9.4 (Unicode Support). Everything seems working except two fields still contains HTML/XML tags.

Any suggestions?