Updating Data in Excel Named Ranges from SAS

Reply
New Contributor
Posts: 3

Updating Data in Excel Named Ranges from SAS

Hi all,


I am trying to figure out how to use the excel libname engine to update data
in excel named ranges. I have been following the examples in two excellent
papers on the topic (see references at bottom of email) but am obtaining
different results than what the authors documented. Specifically, say I
have an excel created named range of a 3X3 block of cells. I then use the
excel libname engine to update this named range; however, the new SAS data
that I am importing to excel is 10X3(RXC)in dimension. The examples in the
papers referrenced below suggest that dimensions of the excel named range
should automatically be updated to include all of the imported data. Hence
the named range should now have dimension 10X3 also. However, instead,
while the data is importing correctly, the named range dimension does not
change. My code is below:


%let path = ;


LIBNAME xl EXCEL "&path\Named Range Template1.xlsx" ;
/* Note that the above referenced excel file already exists with a 3X3 excel
created named range called 'test' */


proc datasets lib = xl nolist;
delete test;
quit;


data xl.test(drop = i);
format date yymmddd10.;
do i = 1 to 10;
  date = today();
  x1 = ranuni(12)*100;
  x2 = ranuni(13)*100;
  output;
end;
run;
LIBNAME xl CLEAR;


I am running 64 bit versions of SAS 9.3 and Excel 2010.


Does anyone have any insights on what I am missing?


Many thanks,


Scott


http://www.phusewiki.org/docs/2010/2010%20PAPERS/CC01%20Paper.pdf
http://www.stratia.ca/papers/excel_libname.pdf

Super User
Posts: 19,855

Re: Updating Data in Excel Named Ranges from SAS

Posted in reply to scottdmurff

Running on 32 bit it works fine, my named range does change to reflect the new dataset size Smiley Happy

I'd suggest opening a track with Tech Support because it might be 'bit' specific issue with Excel.

Respected Advisor
Posts: 4,930

Re: Updating Data in Excel Named Ranges from SAS

Not so fast Smiley Happy ! Try this first :

LIBNAME xl EXCEL "&path\New Range Template1.xlsx" ; /* Must not already exist */

data xl.test(drop = i);
format date yymmdd10.;
do i = 1 to 3;
  date = today();
  x1 = ranuni(12)*100;
  x2 = ranuni(13)*100;
  output;
end;
run;

proc datasets lib = xl nolist;
delete test;
quit;

data xl.test(drop = i);
format date yymmdd10.;
do i = 1 to 10;
  date = today();
  x1 = ranuni(12)*100;
  x2 = ranuni(13)*100;
  output;
end;
run;

LIBNAME xl CLEAR;

Now check the Excel file. You will notice that not only there is a range named test but also a sheet with the same name.  If you created the named range on a sheet named other than test then SAS can't (or will not) update the name definition. That's my theory.

According to this theory, you must either create your named range on a sheet with the same name, or better, let SAS create it.

PG

PG
Super User
Posts: 19,855

Re: Updating Data in Excel Named Ranges from SAS

Actually I think there's a SAS Note somewhere indicating that if you're exporting to named ranges, then you should not use the same name of a Excel worksheet and, in fact, it should not be something that could be confused as a cell reference (B45).

Scott, do you have a worksheet also named Test?

New Contributor
Posts: 3

Re: Updating Data in Excel Named Ranges from SAS

Many thanks for your responses Reeza and PGStats.

PGStats, the resulting excel named range dimensions after running your code did reflect the size of the new larger dataset.  However, I've identified additional strange behavior.  I wanted to look at the dimensions of the named range after the first dataset was written so I cleared the libname to open the excel file.  The dimension of the range was as expected.  However, I then reassigned the libname, deleted the content of the range and inserted the larger dataset.  In this case the named range retains the smaller dimensions.  It appears that after clearing the libname, the range is "locking up."  I'd be interested if you observe the same behavior.  See my code below.

%let path = U:\SAS\EXCEL LIBNAME Engine;

LIBNAME xl EXCEL "&path\New Range Template2.xlsx" ; /* Must not already exist */

data xl.test(drop = i);

format date yymmdd10.;

do i = 1 to 3;

  date = today();

  x1 = ranuni(12)*100;

  x2 = ranuni(13)*100;

  output;

end;

run;

LIBNAME xl CLEAR;

LIBNAME xl EXCEL "&path\New Range Template2.xlsx" ;

proc datasets lib = xl nolist;

delete test;

quit;

data xl.test(drop = i);

format date yymmdd10.;

do i = 1 to 10;

  date = today();

  x1 = ranuni(12)*100;

  x2 = ranuni(13)*100;

  output;

end;

run;

LIBNAME xl CLEAR;

Also, Reeza, in my initial example I did not have a sheet named 'test.'  Rather, the named range 'test' was on 'Sheet1.'  So, it appears that SAS does locate and insert data into named ranges regardless of the sheet name.

I think named range dimensions not updating may indeed have something to do with the 64bit platform.  I have a track in with SAS tech support.  I'll update this thread if I learn anything helpful.

Respected Advisor
Posts: 4,930

Re: Updating Data in Excel Named Ranges from SAS

Posted in reply to scottdmurff

Hi Scott,

I can't reproduce the 'strange' behaviour on the Win32 platform with Excel2007, even when resaving the 3x3 table in Excel before updating it with SAS.

Taking a step back, I have spent quite a bit of time trying to guess the properties of the SAS-Excel interface and came to this grim conclusion: Excel is not a good place to store data. I make a point now of accessing Excel files in ACCESS=READONLY mode, as often as possible. I store my own data in databases.

Best of luck!

PG

PG
Frequent Contributor
Posts: 81

Re: Updating Data in Excel Named Ranges from SAS

Hi PG State,

while using "%let path=E:\SAS DOC\SAS DATA\Engine; LIBNAME xl EXCEL "&path\Template1.xlsx" ;" I am getting error as

ERROR: Connect: Cannot update.  Database or object is read-only.

ERROR: Error in the LIBNAME statement

so could you please help me to understand these errors as well.

Thanks

Super User
Posts: 19,855

Re: Updating Data in Excel Named Ranges from SAS

Posted in reply to AnandSahu

Post your question in a new thread.

Super Contributor
Posts: 644

Re: Updating Data in Excel Named Ranges from SAS

Posted in reply to AnandSahu

This error often occurs if you have the spreadsheet open in Excel and are trying to update it in SAS.  Make sure the spreadsheet is closed (close Excel to be sure) before running your code.

Richard

Frequent Contributor
Posts: 81

Re: Updating Data in Excel Named Ranges from SAS

hi PG Stats,

could you plz explain the functioning of the statement "LIBNAME xl EXCEL "&path\New Range Template1.xlsx" ; /* Must not already exist */"

as  I don't understand the use of "EXCEL" and "&" mentioned in the above statement.

Thanks

Anand

Respected Advisor
Posts: 4,930

Re: Updating Data in Excel Named Ranges from SAS

Posted in reply to AnandSahu

HI!

In the statement LIBNAME xl EXCEL "&path\New Range Template1.xlsx" ;

xl is the name of the library

EXCEL is the name of the SAS engine that will access the data (sometimes SAS can determine which engine to use just by looking at the file extension, but I prefer to state it explicitly)

"&path\New Range Template1.xlsx" is the pathname of the Excel file in which &path is a macro variable that must be already defined with a statement such as:

%let path=c:\users\Anand\documents;

Of course, you could use "c:\users\Anand\documents\New Range Template1.xlsx" in the libname statement.

PG

PG
Frequent Contributor
Posts: 81

Re: Updating Data in Excel Named Ranges from SAS

thanks PG for updating me :-)

Ask a Question
Discussion stats
  • 11 replies
  • 3059 views
  • 6 likes
  • 5 in conversation