unexpected quotation mark in excel output

[Charlie_novar]

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:

[data_null__]

Are you sure it was changed by SAS?

[Charlie_novar]

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;

[data_null__]

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.

[Chevell_sas]

Charlie, make sure that you are using the most recently updated ExcelXP  tagset which you can get from the below location.

Base SAS: ODS MARKUP

[Charlie_novar]

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

[Harmoning · Accepted solution]

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.

[data_null__]

The R1C1 referencing it the answer.  This works.

data toc;
   input panel:$32. loc ord;
   length toc $32;
   toc = cats('=',panel,'!R',loc,'C1');
   cards;
keyxls_saf 3 5
dmstd      3 6
amstd      3 7
rdstd      3 8
;;;;
   run;

data dmstd;
   x = 'This is message';
   output;
   output;
   output;
   run;

ods tagsets.excelxp path='~' file='TOC.xml' options(doc='Help');
ods tagsets.ExcelXp options(sheet_name="TOC" doc='none');
proc report data=toc missing nowindows split = "#";
   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 options(sheet_name='dmstd');
proc print noobs data=dmstd;
   run;
ods tagsets.ExcelXP close;

[Charlie_novar]

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

[data_null__]

Don't worry about all that provided the correct answer I just provided example.  I may want to do this myself someday and I'm glad to know how.

[Charlie_novar]

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.