topic Re: Fetching a data from URL/Website and storing into SAS in New SAS User

Fetching a data from URL/Website and storing into SAS

alokpande28 — Tue, 10 Mar 2020 09:29:43 GMT

Hi All,

I am looking for solution to fetch the data from Website and store it into SAS.

Below is the sample link

https://www.banque-france.fr/statistiques/taux-et-cours/les-taux-de-change-salle-des-marches/autres-cours-de-leuro-fin-de-mois

I would like to fetch the currency table and store it into SAS.

Does anyone have any idea about fetching data from the website and store it into SAS.

Quick help would be appreciates

Re: Fetching a data from URL/Website and storing into SAS

AnandVyas — Tue, 10 Mar 2020 12:01:17 GMT

Hi @alokpande28

When you say SAS, do you mean SAS Data set? I don't think you can insert data like using SQL directly like in database.

One way I can think of is do fetch the data and store it in a csv or txt file and then invoke a SAS Code (Could be Stored process) to create a SAS dataset using the csv/txt.

Re: Fetching a data from URL/Website and storing into SAS

Quentin — Tue, 10 Mar 2020 12:10:50 GMT

I haven't tried it, but for a simple point-and-click for a few tables, this chrome extension might work for you:

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2019/3183-2019.pdf

The other alternative is to scrape the URL, parse the HTML, etc.

Re: Fetching a data from URL/Website and storing into SAS

alokpande28 — Tue, 10 Mar 2020 12:15:58 GMT

Thank you for your response Anand.

One way I can think of is do fetch the data and store it in a csv or txt file and then invoke a SAS Code (Could be Stored process) to create a SAS dataset using the csv/txt ----.> Can you please provide more detail on it? I want to implement complete solution in SAS.

Re: Fetching a data from URL/Website and storing into SAS

alokpande28 — Tue, 10 Mar 2020 12:18:05 GMT

HI Quentin

Thank you for your response it seems like there is manually we can download but I am looking for the solution from the automotive prospective. There should not be any human intervention is needed

Re: Fetching a data from URL/Website and storing into SAS

Tom — Tue, 10 Mar 2020 12:37:37 GMT

Don't use the URL for the human readable page.

Use the link from that page that returns a CSV file.

http://webstat.banque-france.fr/fr/downloadFile.do?id=5385701&exportType=csv

filename csv url 'http://webstat.banque-france.fr/fr/downloadFile.do?id=5385701&exportType=csv';
proc import datafile=csv dbms=csv out=want replace;
run;

Re: Fetching a data from URL/Website and storing into SAS

alokpande28 — Tue, 10 Mar 2020 12:32:00 GMT

Thank you Tom for your response,
I am new to SAS, Can you please let me know how did you make this as URL to downloaded file, and what is the next step after downloading the file.
There are 2 more URL similar I want to use it for another reason

Re: Fetching a data from URL/Website and storing into SAS

Tom — Tue, 10 Mar 2020 12:39:55 GMT

You can make a fileref that uses the URL engine to get the CSV file. Then use PROC IMPORT to convert the file into a dataset. You could also write your own data step to read the data instead of asking PROC IMPORT to guess how to define the variables.

See code I added to previous answer.

Re: Fetching a data from URL/Website and storing into SAS

Tom — Tue, 10 Mar 2020 12:45:49 GMT

To find the better URL what I did was

1) Open the URL you posted.

2) Click on the EN link in the top banner so I could see the page in English.

3) Right click on the link that it had that said something like to see the data click HERE and copy the link address.

4) Paste the link into my SAS program.

Re: Fetching a data from URL/Website and storing into SAS

alokpande28 — Wed, 11 Mar 2020 10:55:13 GMT

Hi Tom Thanks for your info I have tried to import but

I am getting an error. (Import unsucessfull)

below is the log file details for more info.

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 %LET _CLIENTTASKLABEL='Program';
4 %LET _CLIENTPROJECTPATH='';
5 %LET _CLIENTPROJECTNAME='';
6 %LET _SASPROGRAMFILE=;
7
8 ODS _ALL_ CLOSE;
9 OPTIONS DEV=ACTIVEX;
10 GOPTIONS XPIXELS=0 YPIXELS=0;
11 FILENAME EGSR TEMP;
12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
13 STYLE=HtmlBlue
14 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
15 NOGTITLE
16 NOGFOOTNOTE
17 GPATH=&sasworklocation
18 ENCODING=UTF8
19 options(rolap="on")
20 ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
21
22 GOPTIONS ACCESSIBLE;
23 filename csv url 'http://webstat.banque-france.fr/fr/downloadFile.do?id=5385701&exportType=csv';
24 proc import datafile=csv dbms=csv out=want replace;
25 run;

NOTE: Impossible d'ouvrir le catalogue de paramètres: SASUSER.PARMS.PARMS.SLIST en mode mise à jour. Les paramètres temporaires
seront enregistrés dans WORK.PARMS.PARMS.SLIST.
ERROR: The connection has timed out..
ERROR: Import unsuccessful. See SAS Log for details.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE IMPORT used (Total process time):
real time 21.20 secondes
cpu time 0.06 secondes

26
27 --libname exbat excel ''
28
29 GOPTIONS NOACCESSIBLE;
30 %LET _CLIENTTASKLABEL=;
31 %LET _CLIENTPROJECTPATH=;
32 %LET _CLIENTPROJECTNAME=;
33 %LET _SASPROGRAMFILE=;
34
35 ;*';*";*/;quit;run;
36 ODS _ALL_ CLOSE;
37
38
39 QUIT; RUN;
40

Re: Fetching a data from URL/Website and storing into SAS

Vince_SAS — Wed, 11 Mar 2020 11:54:31 GMT

The Web site might have timed out while trying to serve the file.

I think that data in that CSV file might differ from what's shown on the Web page. You might need to find a different source of the data.

Vince DelGobbo

SAS R&D

Re: Fetching a data from URL/Website and storing into SAS

alokpande28 — Thu, 12 Mar 2020 10:44:49 GMT

Thanks for your response, But there is no other source other than this website at this moment becasue which is something related to business.

Re: Fetching a data from URL/Website and storing into SAS

Tom — Thu, 12 Mar 2020 11:58:55 GMT

From the extra lines in your SAS log it looks like you are using some sort of front end to submit your SAS code (like SAS/Studio or Enterprise Guide). Where is the computer that is running SAS? Does it have access to the internet?

You might need to download the files yourself first and have SAS read them as regular files.

Re: Fetching a data from URL/Website and storing into SAS

alokpande28 — Thu, 12 Mar 2020 13:37:25 GMT

Yes Tom, I am using SAS Enterprise Guide.
And it has the internet access as well from the same location.

Re: Fetching a data from URL/Website and storing into SAS

Tom — Thu, 12 Mar 2020 13:40:34 GMT

So you can download other files using the URL engine?

You might want to have your system team test to make sure they don't have some type of block on that website.

Or just try again, perhaps the source server was just down when you tried.

I did not have any trouble getting regular PC SAS to read the URL I posted as a CSV file.