BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Charlie_novar
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Harmoning
Fluorite | Level 6

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

10 REPLIES 10
data_null__
Jade | Level 19

Are you sure it was changed by SAS?

Charlie_novar
Calcite | Level 5

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;

data_null__
Jade | Level 19

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
SAS Employee

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
Calcite | Level 5

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
Fluorite | Level 6

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__
Jade | Level 19

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
Calcite | Level 5

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__
Jade | Level 19

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
Calcite | Level 5

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.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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