The SAS Output Delivery System and reporting techniques

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

Reply
N/A
Posts: 0

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

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!!!
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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.
SAS Super FREQ
Posts: 8,862

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

Posted in reply to 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;
Ask a Question
Discussion stats
  • 3 replies
  • 137 views
  • 0 likes
  • 3 in conversation