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

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

 

 

 

 


Test.JPG
1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

19 REPLIES 19
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
JasonNC
Quartz | Level 8

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.

 

 

Reeza
Super User

Please show your code 

Reeza
Super User

Have you tried formatting using a TAGATTR?

What does your code look like now? 

JasonNC
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

JasonNC
Quartz | Level 8

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


Test.JPG
Reeza
Super User

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. 

Cynthia_sas
SAS Super FREQ

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

JasonNC
Quartz | Level 8

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;


Cyntiatest.JPG
Reeza
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

In your proc report:

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

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

JasonNC
Quartz | Level 8

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

 

 

 

 


Rw9_test.JPG
Reeza
Super User

What version of Tagsets ExcelXP are you using?

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 19 replies
  • 2245 views
  • 4 likes
  • 4 in conversation