The SAS Output Delivery System and reporting techniques

unexpected quotation mark in excel output

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

unexpected quotation mark in excel output

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:

2015-01-28_14-52-15.png

output from excel:

2015-01-28_15-04-03.png


Accepted Solutions
Solution
‎01-29-2015 11:46 AM
Occasional Contributor
Posts: 6

Re: unexpected quotation mark in excel output

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.

View solution in original post


All Replies
Respected Advisor
Posts: 3,788

Re: unexpected quotation mark in excel output

Are you sure it was changed by SAS?

Occasional Contributor
Posts: 5

Re: unexpected quotation mark in excel output

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.

2015-01-28_15-47-40.png

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;

Respected Advisor
Posts: 3,788

Re: unexpected quotation mark in excel output

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.

SAS Employee
Posts: 88

Re: unexpected quotation mark in excel output

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

Occasional Contributor
Posts: 5

Re: unexpected quotation mark in excel output

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

Solution
‎01-29-2015 11:46 AM
Occasional Contributor
Posts: 6

Re: unexpected quotation mark in excel output

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.

Respected Advisor
Posts: 3,788

Re: unexpected quotation mark in excel output

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;
Occasional Contributor
Posts: 5

Re: unexpected quotation mark in excel output

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

Respected Advisor
Posts: 3,788

Re: unexpected quotation mark in excel output

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.

Occasional Contributor
Posts: 5

Re: unexpected quotation mark in excel output

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.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1016 views
  • 3 likes
  • 4 in conversation