I got a problem regarding creating an excel output via SAS. In the sas dataset, the variable value is =dmstd!a3 which I would like to apply this excel function using sas. However, when it outputs to excel file, the value changed to =dmstd!'a3', then the function wouldn't work anyhow. I am wondering why there was this unexpected quotation mark added. Please help me out. Thank you.
Chaoyi
dataset in sas:
output from excel:
Charlie,
Check out SAS documentation for passing Excel formulas instead of values
Base SAS: Demo: ExcelXP Tagset and Microsoft Excel
You also need to switch the default in Excel to see your formula displayed as R1C1.
Understanding R1C1 References (Microsoft Excel)
I haven't used this for formulas that reference another Sheet (only within same sheet), but it should work.
Hope this helps.
Are you sure it was changed by SAS?
I don't know if there was anything to do with SAS. I rather say it was excel who make that change. I am not quite sure. All I did is this.
ods tagsets.ExcelXp options(sheet_name="TOC");
proc report data=toc missing nowindows split = "#"
style(report) = [/*rules = groups*/ cellspacing = 0 cellpadding = 1pt font_face=Arial font_size=8pt]
style(column) = [protectspecialchars = off font_face=Arial font_size=8pt]
style(header) = [protectspecialchars = off font_face=Arial font_size=8pt];
column (" Table of Contents " ord panel toc );
define ord /order order=internal noprint;
define panel / display 'Panel' center style(column) = {cellwidth = 50 pt} style(header) =[just=c];
define toc / display 'Description of the check' center style(column) = {cellwidth = 200 pt} style(header) =[just=c];
run;
ods tagsets.ExcelXP close;
I think for formulas you need to use the TagAttr Style Element:
You will need to consult the HELP file and other online documentation/SGF papers etc. to work out the details.
Charlie, make sure that you are using the most recently updated ExcelXP tagset which you can get from the below location.
Hi Chevell,
Thank you for your respond. I am using the latest version of ExcelXP which is v1.130, 08/02/2013. It's just so weird, when I use hyperlink function, it works well, but when I try to refer to some other cell's value, it adds these quotation marks automatically. I don't know maybe there was some incompatibleness between SAS and Excel.
Charlie
Charlie,
Check out SAS documentation for passing Excel formulas instead of values
Base SAS: Demo: ExcelXP Tagset and Microsoft Excel
You also need to switch the default in Excel to see your formula displayed as R1C1.
Understanding R1C1 References (Microsoft Excel)
I haven't used this for formulas that reference another Sheet (only within same sheet), but it should work.
Hope this helps.
The R1C1 referencing it the answer. This works.
Thank you data_null_,
Harmoning and you both find out the answer, and your method proved that all we need here is R1C1 format. I wish I could give you another CORRECT ANSWER as well.
Thanks again. I really appreciate all of you guys' generous help.
Charlie
Thank you Harmoning,
I looked up the article you referred and it did help me out. Now I use R1C1 instead of A1 style and it works.
Thank you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.