inserting huge formulas into excel using sas or excelxp or sas ods, help !!

[deleted_user]

Hi,

I am trying to insert formulas into a preformatted excel workbook. these formulas are huge and reference other sheets. I found this piece of code online but doesn't work...

code i am using
DATA _NULL_;
FILE SAS2XL;
PUT '[FORMULA.fill("=IF(ISTEXT(F5),F5,SQRT((CK5^2*(BG5/CH5*52)^0*CQ5/5+(CR5/5)^2*BG5^2)))","r6c6:r65c6")]';
RUN;

I am ready to use excel or ods or excelxp

thanks in advance!!!

[sbb]

So, what did it generate as compared to what did you expect it to generate? This information would be helpful so that no one presumes what you truly want to have SAS generate in your SAS program. Also, you may want to search the SAS support http://support.sas.com/ website using your post SUBJECT (minus the "huge" reference). The site has many technical papers and it's very likely the topic has been addressed in a technical document or conference paper.

Scott Barry
SBBWorks, Inc.

[Cynthia_sas]

And, to followup on Scott's suggestion, when you search on the strings
FILE SAS2XL
almost all the hits on support.sas.com have to do with using DDE and SAS to write data to Excel.

If you want to find out about formulas and TAGSETS.EXCELXP, then you would need to search on other strings. This web site might be one such place to start to understand the ExcelXP tagset and sending formats to Excel.
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

However, keep in mind that when you use ODS HTML or ODS TAGSETS.EXCELXP techniques to write files (files which Excel can open), you cannot write to preformatted, already existing, Excel workbooks.

cynthia

[deleted_user]

Try this....


data test(drop = i);
do i = 1 to 10;
a = i;
output;
end;
run;

data test2;
set test ;
b = '=$a$'||compress(put(_n_,8.))||'+1';
c = '=$b$'||compress(put(_n_,8.))||'+2';
run;
options noxwait noxsync;
FILENAME SAS2XL DDE 'EXCEL|SYSTEM' LRECL=650;

/* this opens excel if not open already*/
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;

FILENAME DDECMDS DDE 'EXCEL|sheet1!R5C1:R65000C20' LRECL=650;


DATA _NULL_;
FILE DDECMDS;
set test2 ( firstobs =5);
put a b c;

RUN;