topic Re: unexpected quotation mark in excel output in ODS and Base Reporting

unexpected quotation mark in excel output

Charlie_novar — Wed, 28 Jan 2015 20:06:34 GMT

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:

Re: unexpected quotation mark in excel output

data_null__ — Wed, 28 Jan 2015 20:36:11 GMT

Are you sure it was changed by SAS?

Re: unexpected quotation mark in excel output

Charlie_novar — Wed, 28 Jan 2015 20:49:44 GMT

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;

Re: unexpected quotation mark in excel output

data_null__ — Wed, 28 Jan 2015 21:14:03 GMT

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.

Re: unexpected quotation mark in excel output

Chevell_sas — Wed, 28 Jan 2015 21:54:46 GMT

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

Re: unexpected quotation mark in excel output

Charlie_novar — Thu, 29 Jan 2015 14:26:33 GMT

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

Re: unexpected quotation mark in excel output

Harmoning — Thu, 29 Jan 2015 16:46:23 GMT

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.

Re: unexpected quotation mark in excel output

data_null__ — Thu, 29 Jan 2015 17:23:10 GMT

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;

Re: unexpected quotation mark in excel output

Charlie_novar — Thu, 29 Jan 2015 18:21:47 GMT

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.

Re: unexpected quotation mark in excel output

Charlie_novar — Thu, 29 Jan 2015 18:26:48 GMT

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

Re: unexpected quotation mark in excel output

data_null__ — Thu, 29 Jan 2015 18:31:32 GMT

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.