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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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