DATA Step, Macro, Functions and more

Updating an excel cell range through SAS

Accepted Solution Solved
Reply
Contributor
Posts: 33
Accepted Solution

Updating an excel cell range through SAS

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.


Accepted Solutions
Solution
‎02-08-2013 09:43 PM
Respected Advisor
Posts: 4,641

Re: Updating an excel cell range through SAS

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

PG

View solution in original post


All Replies
Frequent Contributor
Posts: 97

Re: Updating an excel cell range through SAS

hi,

sample of input file required..

Contributor
Posts: 33

Re: Updating an excel cell range through SAS

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-12Dec-12Jan-13Feb-13
0 cycleVolume321566378
Value34245267575656345
1 cycleVolume234544543
Value4546566577548798678
2 cycleVolume1267645
Value43535656565565443
3 cycleVolume3489456
Value45367545765756
4 cycleVolume234546786
Value675747544455543534
5 cycleVolume324987454
Value4323425546564435643

Thanks,

SPR.

Solution
‎02-08-2013 09:43 PM
Respected Advisor
Posts: 4,641

Re: Updating an excel cell range through SAS

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

PG
Contributor
Posts: 33

Re: Updating an excel cell range through SAS

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.

Respected Advisor
Posts: 4,641

Re: Updating an excel cell range through SAS

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

PG
Valued Guide
Posts: 2,174

Re: Updating an excel cell range through SAS

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

Respected Advisor
Posts: 4,641

Re: Updating an excel cell range through SAS

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

PG
Regular Contributor
Posts: 151

Re: Updating an excel cell range through SAS

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. A1Smiley Very Happy10), 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.
Contributor
Posts: 33

Re: Updating an excel cell range through SAS

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 9 replies
  • 1727 views
  • 0 likes
  • 5 in conversation