download excel files from a web site through SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

download excel files from a web site through SAS

Hi,

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


Accepted Solutions
Solution
‎03-14-2013 09:34 AM
N/A
Posts: 1

Re: download excel files from a web site through SAS

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;

View solution in original post


All Replies
Super User
Posts: 5,429

Re: download excel files from a web site through SAS

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.

Data never sleeps
Occasional Contributor
Posts: 11

Re: download excel files from a web site through SAS

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.

Super Contributor
Posts: 276

Re: download excel files from a web site through SAS

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

Occasional Contributor
Posts: 11

Re: download excel files from a web site through SAS

Posted in reply to kuridisanjeev

Hi Kuri,

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

PROC Star
Posts: 1,167

Re: download excel files from a web site through SAS

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

Occasional Contributor
Posts: 11

Re: download excel files from a web site through SAS

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.

screenshot.pngscreenshot.png

Super Contributor
Posts: 276

Re: download excel files from a web site through SAS

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

Super Contributor
Posts: 276

Re: download excel files from a web site through SAS

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

Super Contributor
Posts: 276

Re: download excel files from a web site through SAS

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

Occasional Contributor
Posts: 11

Re: download excel files from a web site through SAS

Posted in reply to kuridisanjeev

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

Super Contributor
Posts: 276

Re: download excel files from a web site through SAS

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

Solution
‎03-14-2013 09:34 AM
N/A
Posts: 1

Re: download excel files from a web site through SAS

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;

Occasional Contributor
Posts: 11

Re: download excel files from a web site through SAS

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

Super Contributor
Posts: 276

Re: download excel files from a web site through SAS

Hi Aniket.

Sounds great..

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

Regards.

Sanjeev.K

🔒 This topic is solved and locked.

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

Discussion stats
  • 19 replies
  • 5285 views
  • 4 likes
  • 6 in conversation