Download a code generated csv from internet

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Download a code generated csv from internet

Hi guys,

I'm trying to import a csv data from the Brazilian Central Bank homepage.

the location is http://www4.bcb.gov.br/pec/taxas/port/ptaxnpesq.asp?id=txcotacao.

It has a link that generates a csv file on demand. The link shows the following command:

https://www3.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVFechamentoMoedaNoPeriodo&Chk...

The bolded text "01/01/2010" and "17/02/2014" contains the startdate and the enddate of the corresponding dollar exchange rate.

I could make a automated downloader with this, but the problem is that this command does not show any filename. So I can't download it through a filename statement in a regular way. The generated filename is CotacoesMoedasPeriodo.csv.

Any clue?


Accepted Solutions
Solution
‎02-18-2014 03:44 PM
SAS Super FREQ
Posts: 8,744

Re: Download a code generated csv from internet

Hi:

  The first example just reads the file and echoes it back, like Tom's program. The second example using INFILE and INPUT to read the data, which is semi-colon delimited. I show 1038 "records" in the file. It is NOT a  CSV file, BTW, the delimiter is a semi-colon and the comma is used as a decimal separator instead of a period. I just used "quick" variable names var1, var2, etc ... you can change those as needed.

Cynthia

** 1) read everything and just echo it to the listing window;
FileName Bacen URL
"https://www3.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVFechamentoMoedaNoPeriodo&Chk..."
  debug;
    
  title '1) BACEN bank delimited file';
  ods _all_ close;
ods listing;
   data _null_;
     infile bacen;
     file print;
     input;
     put _infile_;
   run;
   
   /* first 2 obs look like this
04012010;220;A;USD;1,7232;1,7240;1,0000;1,0000
05012010;220;A;USD;1,7219;1,7227;1,0000;1,0000
*/
** 2) now after understanding the layout, read the data;
title '2) BACEN bank delimited file';
ods _all_ close;
ods listing;
   data bacen_sas;
     infile bacen dlm=';' ;
     input var1 : ddmmyy10. var2 var3 $ var4 $ var5 : commax6.
           var6 : commax6. var7 : commax6. var8 : commax6.;
   run;
   
** Now print the data;
proc print data=bacen_sas (obs=10);
  title '2) After reading with INFILE/INPUT';
  format var1 ddmmyy10.;
run;

View solution in original post


All Replies
Super User
Super User
Posts: 6,502

Re: Download a code generated csv from internet

What did you try?  Did you try using the URL engine?

data _null_;

part1='https://www3.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVFechamentoMoedaNoPeriodo';

part2='&ChkMoeda=61&DATAINI=01/01/2010&DATAFIM=17/02/2014';

fname=cats(part1,part2);

infile xx url filevar=fname ;

input ; list;

stop;

run;

Occasional Contributor
Posts: 11

Re: Download a code generated csv from internet

Yes, I did, something as follows (the SAS server is upgrading right now so i can't really check the outcomes)

FileName Bacen URL "

https://www3.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVFechamentoMoedaNoPeriodo&Chk..."

then i tried proc import data= Bacen.

The response was something like "there's no file work.bacen".  I figure this happened because the link does not forward directly to a file, but rather it responds with an download request. I searched solutions for this kinda of case but couldn't find something that would solve my case ( I read about Curl, but I can't intall it in my station because i don't have adm rights. So I don't even know if it is going to solve my problem or not).

Super User
Super User
Posts: 6,502

Re: Download a code generated csv from internet

That is because PROC IMPORT is stupid.  Either just read it yourself using a data step or use a data step to copy it to a temporary file for PROC IMPORT to read.

filename in url ..... ;

filename out temp;

data _null_ ;

   infile in lrecl=20000;

   file out lrecl=20000;

   input;

   put _infile_;

run;

proc import datafile="%sysfunc(pathname(out))" dbms=csv ....

Occasional Contributor
Posts: 11

Re: Download a code generated csv from internet

Lol, okay, I will try to import to a temporary and see if it turns out ok (after maintenance is done). Thank you for pointing me a direction. If it works, that means that URL engine is much more powerful than I thought. And proc import less than I thought.

Solution
‎02-18-2014 03:44 PM
SAS Super FREQ
Posts: 8,744

Re: Download a code generated csv from internet

Hi:

  The first example just reads the file and echoes it back, like Tom's program. The second example using INFILE and INPUT to read the data, which is semi-colon delimited. I show 1038 "records" in the file. It is NOT a  CSV file, BTW, the delimiter is a semi-colon and the comma is used as a decimal separator instead of a period. I just used "quick" variable names var1, var2, etc ... you can change those as needed.

Cynthia

** 1) read everything and just echo it to the listing window;
FileName Bacen URL
"https://www3.bcb.gov.br/ptax_internet/consultaBoletim.do?method=gerarCSVFechamentoMoedaNoPeriodo&Chk..."
  debug;
    
  title '1) BACEN bank delimited file';
  ods _all_ close;
ods listing;
   data _null_;
     infile bacen;
     file print;
     input;
     put _infile_;
   run;
   
   /* first 2 obs look like this
04012010;220;A;USD;1,7232;1,7240;1,0000;1,0000
05012010;220;A;USD;1,7219;1,7227;1,0000;1,0000
*/
** 2) now after understanding the layout, read the data;
title '2) BACEN bank delimited file';
ods _all_ close;
ods listing;
   data bacen_sas;
     infile bacen dlm=';' ;
     input var1 : ddmmyy10. var2 var3 $ var4 $ var5 : commax6.
           var6 : commax6. var7 : commax6. var8 : commax6.;
   run;
   
** Now print the data;
proc print data=bacen_sas (obs=10);
  title '2) After reading with INFILE/INPUT';
  format var1 ddmmyy10.;
run;

Occasional Contributor
Posts: 11

Re: Download a code generated csv from internet

Geez, Cynthia, thanks, you actually entered the site and figured everything out. Now I can probably extrapolate the same code for many other stuffs. Thanks again, could not hope for more!

PS: It's a CSV in Brazilian format. I have no idea why we had to change "," for ";" but what can I do?

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 660 views
  • 3 likes
  • 3 in conversation