BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aniket
Calcite | Level 5

Hi,

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

1 ACCEPTED SOLUTION

Accepted Solutions
LouisS
Calcite | Level 5

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

19 REPLIES 19
LinusH
Tourmaline | Level 20

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
Aniket
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

Hi Kuri,

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

TomKari
Onyx | Level 15

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
Calcite | Level 5

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

kuridisanjeev
Quartz | Level 8

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
Quartz | Level 8

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
Quartz | Level 8

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
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

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
Calcite | Level 5

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

kuridisanjeev
Quartz | Level 8

Hi Aniket.

Sounds great..

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

Regards.

Sanjeev.K

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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