topic Re: download excel files from a web site through SAS in SAS Programming

download excel files from a web site through SAS

Aniket — Thu, 14 Mar 2013 07:31:43 GMT

Hi,

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

Re: download excel files from a web site through SAS

LinusH — Thu, 14 Mar 2013 11:05:41 GMT

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.

Re: download excel files from a web site through SAS

Aniket — Thu, 14 Mar 2013 12:08:46 GMT

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_prod_list.xls";

I used this method but nothing working out.

Re: download excel files from a web site through SAS

kuridisanjeev — Thu, 14 Mar 2013 13:22:36 GMT

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

Re: download excel files from a web site through SAS

LouisS — Thu, 14 Mar 2013 13:34:21 GMT

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;

Re: download excel files from a web site through SAS

kuridisanjeev — Thu, 14 Mar 2013 13:41:43 GMT

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

Re: download excel files from a web site through SAS

TomKari — Fri, 15 Mar 2013 01:34:58 GMT

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=fap%5Fval%5Finvalid%5Fpgw%5Fcode&fuseaction=FIND_A_PRODUCT.showFAPHome

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

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 06:11:07 GMT

Hi Louiss,

I have tried your method but it's showing error

see the log:

FILENAME website URL 'http://www.energystar.gov/index.cfm?fuseaction=find_a_product.showProductGroup&pgw_code=FRZ/Freezers Product List.xls';

29 FILENAME COPY "I:\Newfolder\abc.xls";

30 data _NULL_;

31 n=-1;

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

33 file copy recfm=n;

34 input;

35 put _infile_ $varying32767. len;

36 run;

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

ERROR: Hostname www.energystar.gov not found.

NOTE: UNBUFFERED is the default with RECFM=N.

NOTE: The file COPY is:

Filename=I:\Newfolder\abc.xls,

RECFM=N,LRECL=256,File Size (bytes)=0,

Last Modified=15Mar2013:11:35:32,

Create Time=15Mar2013:11:34:31

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

NOTE: DATA statement used (Total process time):

real time 4.56 seconds

cpu time 0.11 seconds

38 filename copy clear;

NOTE: Fileref COPY has been deassigned.

40 libname Excel "I:\Newfolder\abc.xls" ;

ERROR: Connect: External table is not in the expected format.

ERROR: Error in the LIBNAME statement.

42 Data WORK.PROCtoNDC;

43 * Because the worksheet name is

44 the SAS Dataset Name and it has a non standard format you need it in quotes.;

45 SET Excel."Freezers Product List"n;

ERROR: Libname EXCEL is not assigned.

46 run;

NOTE: Compression was disabled for data set WORK.PROCTONDC because compression overhead would increase the size of the data set.

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

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

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

NOTE: DATA statement used (Total process time):

real time 0.02 seconds

cpu time 0.03 seconds

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 06:12:56 GMT

Hi Kuri,

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

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 06:19:26 GMT

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

Re: download excel files from a web site through SAS

kuridisanjeev — Fri, 15 Mar 2013 08:38:11 GMT

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

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 09:28:32 GMT

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.

Re: download excel files from a web site through SAS

kuridisanjeev — Fri, 15 Mar 2013 10:01:42 GMT

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

Re: download excel files from a web site through SAS

kuridisanjeev — Fri, 15 Mar 2013 10:20:36 GMT

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

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 10:38:32 GMT

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

Re: download excel files from a web site through SAS

kuridisanjeev — Fri, 15 Mar 2013 10:43:48 GMT

Hi Aniket.

Sounds great..

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

Regards.

Sanjeev.K

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 11:00:09 GMT

Hi kuri,

i have tried your link for the excel file and its working perfectly and the code which i have already marked for the correct answer.

Can you please tell me how could i get the right link like you have posted. Thanks a lot.....

Re: download excel files from a web site through SAS

kuridisanjeev — Fri, 15 Mar 2013 11:23:18 GMT

Hi Aniket.

Its Pretty Simple..

Right Click On "Excel" in that site and select Copy Link location..paste that link in your Code,, that's it 🙂

Attached Screenshot..

Regards.

Sanjeev.K

Re: download excel files from a web site through SAS

Aniket — Fri, 15 Mar 2013 11:45:50 GMT

Thanks a lot

Re: download excel files from a web site through SAS

_n_p_t_ — Wed, 27 Mar 2013 22:23:44 GMT

Anybody tried downloading from google doc?