download excel files from a web site through SAS

[Aniket]

Hi,

I need to download some excel files from a web site. i wanted to do it with sas. is there any way out?

[LinusH]

Do you wish to import it to SAS as well? As long as you can refer to the Excel-file as a filename with a path, there a ways. Describe your situation in more detail (like the how the Excel-file can be assessed, http address etc).

If not, then I can't see the point of using SAS.

[Aniket]

Yes, I need to download some product list from energystar web site repeatedly and after that import it to sas as well.The files can be access through the path below:

ENERGY STAR Qualified Products : ENERGY STAR ->Find energy star product->There are many types  of products

here i need to download twenty of them. What I used to do is clicking on each product and save the excel file in my hard drive manually. I would like to do this whole process through sas.

filename dehumid url "http://www.energystar.gov/index.cfm?fuseaction=find_a_product.showProductGroup&pgw_code=DE/dehumid_p...";

I used this method but nothing working out.

[kuridisanjeev]

Hi @Aniket.

Here is the another alternative..

proc http out=clindata

url="https://www.dmepdac.com/docs/crosswalk/march_13/2013-03-05_xwalkfinalversion.xls"

webUserName="*******"

webPassword="***************"

method="get"

;

run;

If the website don't asks any login credentials,then you no need to use   webUserName,webPassword.

Not tested above code.but hope that will helps to you.

Regards.

Sanjeev.K

[Aniket]

Hi Kuri,

I have tried your method but my sas version doesn't support the Proc Http.

[TomKari]

I tried the url you provided a couple of ways:

1. When I just dropped it into my browser, it redirected me to www.energystar.gov/index.cfm?pgw_code=DE%2Fdehumid%5Fprod%5Flist%2Exls&resultsPerPage=50&message_id=...

2. When I tried reading it in SAS, I got basically an empty dataset.

I think your link might be incorrect. Are you sure it works?

Tom

[Aniket]

Hi Tom,

in the attached image below you can see on the first there are different category,in which i used to click on each category and download the excel file manually(marked as red).

You can also see the web address there.It works perfectly when i do it manually.

[kuridisanjeev]

Hi Aniket..

Have you tried with bellow link???

http://downloads.energystar.gov/bi/qplist/dehumid_prod_list.xls?b9a2-770c

If not try once and let me know.

Regards.

Sanjeev.K

[kuridisanjeev]

And moreover,in your Excel file,first 4 rows containing Header columns.So you have to Use Firstobs=6 in your Infile statement.

Regards.

Sanjeev.K

[kuridisanjeev]

Hi @Aniket

You Can Use Filename Statement to do that i guess.

Here is the small example ..

FILENAME myurl URL 'http://www.masil.org/archives/smoking.txt';

DATA js.smoking;

   INFILE myurl FIRSTOBS=34;

   INPUT state $ cigar bladder lung kidney leukemia area;

RUN;

Regards.

Sanjeev.K

[Aniket]

Hi kuri,

I have tried your first method as well but still getting error massage :

NOTE: Unable to connect to host www.energystar.gov. Check validity of host name.

ERROR: Hostname www.energystar.gov not found.

NOTE: The SAS System stopped processing this step because of errors.

WARNING: The data set WORK.BB may be incomplete.  When this step was stopped there were 0 observations and 17 variables.

WARNING: Data set WORK.BB was not replaced because this step was stopped.

NOTE: DATA statement used (Total process time):

      real time           4.53 seconds

      cpu time            0.07 seconds

[kuridisanjeev]

HI @Aniket

As mentioned by Tomkari, your link might be incorrect. Are you sure it works?

I too tried to open your provided link,it is redirecting to http://www.energystar.gov site ,not to the excel file.

Please check and let me know.

Regards.

Sanjeev.K

[LouisS · Accepted solution]

You need to copy the Excel File byte by byte and then use SAS Access for PC FIles as in the example below, or SAS Access for ODBC to read the native Excel.  You can also use SAS Enterprise Guiide to import the File to a SAS Dataset.

FILENAME website URL 'https://www.dmepdac.com/docs/crosswalk/march_13/2013-03-05_xwalkfinalversion.xls';

FILENAME COPY "C:\temp\TestPROCtoNDC.xls";

  data _NULL_;

   n=-1;

      infile website recfm=s nbyte=n length=len;

     file copy recfm=n;

     input;

     put _infile_ $varying32767. len;

run;

filename copy clear;

libname Excel "C:\temp\TestPROCtoNDC.xls" ;

Data WORK.PROCtoNDC;

* Because the worksheet name is
the SAS Dataset Name and it has a non standard format you need it in quotes.;

       SET Excel."03-05-2013 NDC-HCPCS XWalk$"n;

  run;

[Aniket]

Thank you all.......It is working perfectly.....

[kuridisanjeev]

Hi Aniket.

Sounds great..

Just curious Which solution worked out for you from above????are you using same Link??

Regards.

Sanjeev.K