|
Tzachi asks:
> I am using dde to export SAS data sets into excel. However,
> after doing that, i would also like to perform some excel
> command like adding a column in the new excel sheet with
> formulas (e.g. =concatenate (a1,b1), Average(a1:c1)). I would
> liek these formulas to fill a new column based on the number
> of rows in the newly created excel file.
On one hand, you could probably perform any data manipulation
first in SAS, and export the results along with your data to
an Excel workbook. On the other hand, your question is per-
fectly legitimate, so see below for a solution ...
> How do i do that from within the sas code? I looked at the
> help file (macrofu.exe) and there seems to be a FORMULA
> function. The problem is that i am not too familiar with the
> syntax in SAS and I have not found examples that apply to this
> specific situation.
Seems as though you were almost there ... What you'll need
is the formula.fill function. Have a look at the sample code
below, where I add a column with the Excel average function
to a sheet with three numerical columns:
%* Define the tab-character. *;
%let tab='09'x;
%* Launch the Excel application if necessary ... *;
options noxsync noxwait xmin;
filename sas2xl dde 'excel|system';
data _null_;
length fid rc start stop time 8;
fid=fopen('sas2xl','s');
if (fid le 0) then do;
rc=system('start excel');
start=datetime();
stop=start+10;
do while (fid le 0);
fid=fopen('sas2xl','s');
time=datetime();
if (time ge stop) then fid=1;
end;
end;
rc=fclose(fid);
run;
%* Some dummy numerical data ... *;
data _bunch_of_numbers(drop=i);
length x y z 8;
do i=1 to 100;
x=5*ranuni(0);
y=10*rannor(0);
z=ranpoi(0,3);
output;
end;
run;
%* Make a blank workbook, save somewhere to fixate the file-name. *;
data _null_;
file sas2xl;
put '[new(1)]';
put '[error(false)]';
put '[save.as("c:\temp\Some Bunch of Numbers")]';
run;
%* Define triplet-style DDE fileref. *;
filename recrange dde
'excel|[some bunch of numbers.xls]sheet1!r1c1:r100c3' notab;
%* Dump the dummy data there. *;
data _null_;
set _bunch_of_numbers;
file recrange;
put
x &tab
y &tab
z;
run;
%* Write an Excel function in the next column, showing the *;
%* average of the three cells to its left. *;
data _null_;
file sas2xl;
put '[formula.fill("=average(rc[-3]:rc[-1])","r1c4:r100c4")]';
run;
%* Save the modified workbook. *;
data _null_;
file sas2xl;
put '[save]';
run;
I assume that toying with this example will give you enough
information to proceed. Should you experience further problems
however, feel free to report on them.
The above works fine for me on WinNT (SP6), with SASv8.2 and
Excel as in Office97.
Kind Regards,
Koen.
---------------------------------
Koen Vyverman
Database Marketing Manager
Fidelity Investments - Luxembourg
---------------------------------
| 
