topic extract website list table using PROC HTTP in SAS Programming

extract website list table using PROC HTTP

peterluan — Fri, 16 Apr 2021 14:50:29 GMT

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;

Re: extract website list table using PROC HTTP

ChrisHemedinger — Fri, 16 Apr 2021 14:57:32 GMT

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

Re: extract website list table using PROC HTTP

peterluan — Tue, 27 Apr 2021 21:38:31 GMT

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={ABF6A9AF-BDE0-4FB9-8741-098CF5C65EBE}&View={254D807D-74C7-44F9-8C33-341527143136}&RowLimit=0&RootFolder=%2fsites%2fcorprisk%2dopsrisk%2d038%2fLists%2fAlternative%20Practice%20Request'

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?