The SAS Output Delivery System and reporting techniques

ODS tagsets Excel Xp format question

Accepted Solution Solved
Reply
Regular Contributor
Posts: 154
Accepted Solution

ODS tagsets Excel Xp format question

Hi,

 

I am generating the report using proc report & odsexcelxp

Is there any format i can use to create data like shown in the attached jpeg file.

I want to show value formatted without decimal but when the user clicks on the cell they can see the whole value with decimals

 

 

 

 

Attachment

Accepted Solutions
Solution
‎02-01-2017 10:48 PM
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: ODS tagsets Excel Xp format question

Yes, typo on my part here.  The equals should be a colon:

Data Dolr_test;
  input Agent $ Dolrs 15.2;
datalines;
George 31918175.55
Adam 9013533.98
Danny 59407.72
;
run;
 
ods tagsets.excelxp file = "s:\temp\rob\Dolr_test.xml" ;
proc report data=Dolr_test split='*' ;
  column Agent Dolrs;
  define Agent/ order order=data;
  define Dolrs / " $ Dollars * (000)" style={tagattr="Format:[$$-409]#,##0"};
run;
ods tagsets.excelxp close;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: ODS tagsets Excel Xp format question

This is not really a SAS question.  This is standard Excel functionality that, if you apply a format to the cell, then the underlying value is hidden and the formatted value is displayed (it actually works exactly the same in SAS, real value is hidden formatted value is shown).  If you do not want the whole value to be retained then you would need to int(), floor(), ceil(), or round the value before outputting the data via proc report, e.g.

data want;
  set have;
  dollar=int(dollar);
run;
Regular Contributor
Posts: 154

Re: ODS tagsets Excel Xp format question

Hi Thanks for the reply you are right.

 

When i export the data to excel it displays the formatted value.

 

The thing i am trying to achieve is it should display values in format but when i click on the cell it show full value.

 

 

Grand Advisor
Posts: 16,393

Re: ODS tagsets Excel Xp format question

Please show your code 

Grand Advisor
Posts: 16,393

Re: ODS tagsets Excel Xp format question

Have you tried formatting using a TAGATTR?

What does your code look like now? 

Regular Contributor
Posts: 154

Re: ODS tagsets Excel Xp format question

Hi Reeza,

 

I used tagttr format.It displays the cells in formatted value. What I am trying to achieve is the excel should show the formatted value but when i click on the cell it should show full value.

 

I want to achieve this with out changing excel setting manually

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: ODS tagsets Excel Xp format question

So what exactly do you mean.  You export from SAS to Excel with tagattr setting the format to $ yes?  And this value in the sheet shows as $xyz correctly yes.  But, you want, when you select the cell, the formula bar to show $xyz rather than xyz.  If so, then you would need to change the variable to text and display it as text.  This is standard Excel functionality, value is formatted in the sheet, not in the formular bar, the only way to change it would be to set it as text:

data want;
  set have;
  string_value=put(int(num_value),dollar8.);
run;

ods...
proc report...
  columns string_value;
  define...
run;
ods...

This way the variable will be string, and then everything displays as a string.

Regular Contributor
Posts: 154

Re: ODS tagsets Excel Xp format question

Hey

 

Sorry if you misunderstood my question

 

What i am expecting is if i format $xyz to display without decimals or formatting the million to display as thousands(000) format it should display in the cell with that format but when the user clicks on the cell he should able to see the full value with decimals

 

I attached shot of what i am expecting

Attachment
Grand Advisor
Posts: 16,393

Re: ODS tagsets Excel Xp format question

We ( or at least I) understand what you want. I'm still waiting on your current code, preferably with the tagattr and with some sample data to actually run it. I'm fairly certain TAGATTR does what you want. 

SAS Super FREQ
Posts: 8,645

Re: ODS tagsets Excel Xp format question

Hi:

  It appears to me that Reeza is correct, TAGATTR changes the displayed value. When you click in the cell, you see the real value, as shown below. I added the "unfmt" columns to the report so you could see what Excel displays when I click into the cell.

cynthia

testfmt_xp.png

Regular Contributor
Posts: 154

Re: ODS tagsets Excel Xp format question

Thanks for your patience and support.

The output is not displayed in 000 fomat.I want the amounts in milion to be displayed in 000 format .When the user clicks he can see the whole value

 

I attached screen shot of output

 

Data Dolr_test;
input Agent $ Dolrs 15.2;
datalines;
George 31918175.55
Adam 9013533.98
Danny 59407.72
;
run;

 

ods tagsets.excelxp file = "C:\Dolr_test.xml" ;

proc report data=Dolr_test split='*' ;
column Agent Dolrs;

define Agent/ order order=data;
define Dolrs / display " $ Dollars * (000)"  style(column)={tagattr="Format:$###,###"};

run;

ods tagsets.excelxp close;

Attachment
Grand Advisor
Posts: 16,393

Re: ODS tagsets Excel Xp format question

The code I posted is 99% of what you need. Find the appropriate Excel format and replace the current value.

 

Out of curiosity are any of your users ever female? You always refer to them as he.

Esteemed Advisor
Esteemed Advisor
Posts: 6,726

Re: ODS tagsets Excel Xp format question

In your proc report:

define dollar_amount / "Label" style={tagattr='format="[$$-409]#,##0"'};

The above should set dollar_amount to be $, with no decimals.  

Regular Contributor
Posts: 154

Re: ODS tagsets Excel Xp format question

Thanks for your patience and support.Unfortunately it didn't work. I attached the output screenshot

 

Data Dolr_test;
input Agent $ Dolrs 15.2;
datalines;
George 31918175.55
Adam 9013533.98
Danny 59407.72
;
run;

 

ods tagsets.excelxp file = "C:\Dolr_test.xml" ;

proc report data=Dolr_test split='*' ;
column Agent Dolrs;

define Agent/ order order=data;
define Dolrs / " $ Dollars * (000)" style={tagattr='format="[$$-409]#,##0"'};

run;

ods tagsets.excelxp close;

 

I tested but it didn't work. I attached the output how it displayed.

 

I want to display the amount in millions in thousands (000) format but when the user clicks on it he can see the whole value

 

 

 

 

Attachment
Grand Advisor
Posts: 16,393

Re: ODS tagsets Excel Xp format question

What version of Tagsets ExcelXP are you using?

 

Post a Question
Discussion Stats
  • 19 replies
  • 260 views
  • 4 likes
  • 4 in conversation