BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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!!!
3 REPLIES 3
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
SAS Super FREQ
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
Not applicable
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 705 views
  • 0 likes
  • 3 in conversation