Updating an excel cell range through SAS

[RaviSPR]

Hi,

I am using Named Range concept of Excel and SAS Libname to update the excel's cell ranges. Below is the code. Before running this code, i have created Named Data range1 in Excel manually.

Libname xl "...\tamplate.xls";

Proc datasets;

delete Datarange1;

run;

Data xl.datarange1;

set work.emp;

run;

Here the problem is, it is working for a fixed range of Named range which has defined by developer in excel manually which is tedious to create the DataRanges of 30 or 40 for the next 1year in excel file.

For suppose, if i want to put the data to next columns in the excel when compared with previous columns, the above concept is not working.i.e updating the columns every day to the next beside column wont work.

So, is there any way to define Named Range  Concept in excel from SAS??

Thanks,

SPR.

[allurai0412]

hi,

sample of input file required..

[RaviSPR]

Hi,

Please find below for the format of the file. Every month i need to update the monthly data in the next columns. I can create Data ranges in excel before running sas code. But like this, it requires to create for the next 20 or 30 columns=20 or 30 Data Ranges for every month in excel file which is tedious. so, i am thinking to create these DataRanges through SAS if possible??

Delinq		Nov-12	Dec-12	Jan-13	Feb-13
0 cycle	Volume	321	566	378
	Value	342452	675756	56345
1 cycle	Volume	234	544	543
	Value	454656	657754	8798678
2 cycle	Volume	12	676	45
	Value	43535	656565	565443
3 cycle	Volume	34	89	456
	Value	4536	7545	765756
4 cycle	Volume	234	546	786
	Value	675747	5444555	43534
5 cycle	Volume	324	987	454
	Value	4323425	546564	435643

Thanks,

SPR.

[PGStats · Accepted solution]

Hi, As you probably already figured out, not everything works the way it should at the SAS-Excel interface. Here are a few points to know (learned by way too much trial and error):

When you create a new table (with a new name) in an Excel workbook, with something like

libname xl Excel "c:\myWorkBook.xlsx";

data xl.myNewTab;

set sashelp.class;

run;

libname xl clear;

a new sheet and a new named range are added to the workbook, both with the same name (myNewTab).

You can delete a table from a workbook with something like

libname xl Excel "c:\myWorkBook.xlsx";

proc sql;

drop table xl.myNewTab;

quit;

libname xl clear;

That removes the table and the named range but leaves the sheet named myNewTab.

If you want to reuse that name, you must be very careful. There are a subtle set of rules which I haven't figured out completely that will prevent you from doing so, sometimes.

One thing is certain, the only way to expand your table is to read in into SAS, add the new column to the SAS dataset and either delete the Excel table before you write the new version back or write the new version under a new name.

Good luck.

PG

[RaviSPR]

Hi PG,

Hope u doing good..

I have been using the above concept of Libname-excel for updating the data in excel files.

But, i have got an issue now.

It couldnt able to populate the formats of the dataset which contains in SAS into excel-defined named range...!! and zeros too..which were showing in the sas dataset. Bt couldnt able to populate to excel sometimes.

For EX:: the dataset contains as below.

name  sal

xxx     $4300.98

yyy     $2300.00

sss     0

ddd     $1900.00

ggg     0

If I use Libname - Datarange concept to populate the above data into an excel file, the output excel file is shown as below.

name     sal

xxx     4300

yyy     2300

sss    

ddd     1900

ggg    

which is missing all the required formats. I tried by pre-defining all these formats in excel dataranges. But no use.

And missing these zeros here is very strange for me.

Can you please help me in this..how to get exact formats into the defined Excel DataRanges..??

Many Thanks,

SPR.

[PGStats]

Hi SPR,

Here is a little experiment I just did :

data test;
input name $ sal;
format sal dollar8.2;
datalines;
xxx     4300.98
yyy     2300.00
sss     .
ddd     1900.00
ggg     0
;

option missing=.; /* This is the default for option missing= */
proc print; run;
option missing=0; /* Make missing values print as zeros */
proc print; run;
option missing=.;

libname xl Excel "&sasforum.\datasets\RaviSPR.xlsx";

proc sql;
create table xl.small as
select * from test;
quit;

/* Here I get the messages
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: Table XL.small created, with 5 rows and 2 columns.
*/

libname xl clear; /* Free the file */

/* Here I use Excel outside of SAS to assign a monetary format to column sal */

/* Then I delete and overwrite the Excel table */

libname xl Excel  "&sasforum.\datasets\RaviSPR.xlsx";
proc sql;
drop table xl.small;
create table xl.small as
select name, sal+1 as sal from test;
quit;

libname xl clear;

/* sal column values have been updated and monetary format is preserved */

PG

[Peter_C]

Perhaps it is obvious but i can confirm similar behaviour with SAS/ACCESS ro ODBC.

[PGStats]

My understanding is that ultimately, all Excel data table accesses are done by the Microsoft Ace Excel engine. Other access methods are built on top of that library. - PG

[Keith]

I'm not sure I fully understand your question, but below are a few things I've learned when sending SAS data to Excel named ranges. I'm not aware of a way to create named ranges using SAS (unless you can send over VBA code)

• SAS will only recognise simple named ranges (e.g. A1:D10), so dynamic ranges, e.g. using the OFFSET or INDIRECT functions, are not recognised.
• If you replace data in a named range with a larger range of data, then the named range will automatically increase.
• You can't replace data in Excel without deleting it first (as in your code), but did you know you can use MODIFY in a data step to update existing Excel values with those in a data set?  You can also append new rows to the Excel data using PROC DATASETS, without needing to delete the Excel data first.
  

[RaviSPR]

Hi Keith,

Much Thanks for the reply.

yes. currently I am using this Named Range concept for the reports where the data is replacing every day by creating Named ranges in excel file..

But, I also requires updating data to Right side columns instead of fixed columns. Please see the above Template of my excel file.

Anyways, i will try to use VBA macros whcih can also be run from sas.

Thanks,

SPR.