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
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;
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;
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.
Please show your code
Have you tried formatting using a TAGATTR?
What does your code look like now?
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
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.
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
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.
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
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;
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.
In your proc report:
define dollar_amount / "Label" style={tagattr='format="[$$-409]#,##0"'};
The above should set dollar_amount to be $, with no decimals.
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
What version of Tagsets ExcelXP are you using?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.