Proc report: digits with char-format in parentheses

[Konkordanz]

Hi,

ive made a dataset and changed the format of all columns with digits into char-format. Example:

Insgesamt_sum_char = put(Insgesamt_sum,numx20.);

Reason: If the digit (of a the variable Insgesamt_fz) is <71, the cell should get a "/" instead of the numeric result. Is the result >71 and <119 then put the result into parentheses.For example:

if Insgesamt_fz <71 then do;
     Insgesamt_sum_char="/";end;

if Insgesamt_fz	>=71 and Insgesamt_fz <=119 then do;
	Insgesamt_sum_char="("||strip(Insgesamt_sum_char)||")";
	end;

Afterwards I export the result into a excel-file.

This works pretty well and I get the correct results in a nice file (and it wouldnt work, if the column-format would be numeric). But nevertheless I have a problem:

I open the exported excel-file and click into a Cell with a parenthesized value. By unselecting that cell, the parenthesized value changed into a minus value. For example:

The correct value is (1,0). By clicking and unselecting that cell it changed to -1 (parentheses vanished).

Why is that happen? Do you know a way, to avoid this?

[PaigeMiller]

Please do not show us partial data step code. Show us the whole data step code from now on. Also, please provide sample data as working SAS data step code. 

When I do this, I get what appears to be what you want in Excel

data a;
    numvar=0.523409; output;
    numvar=112.09; output;
    format numvar numx20.;
run;
data b;
    set a;
    length insgesamt_sum_char $ 20;
    if numvar <71 then insgesamt_sum_char="/";
    else if numvar >=71 and numvar <=119 then
    	insgesamt_sum_char="("||strip(numvar)||")";
run;
ods excel file="test.xlsx";
proc report data=b;
    columns numvar insgesamt_sum_char;
    define numvar/order=data;
run;
ods excel close;

[Konkordanz]

Okay, I will do it. But first: What happens if you doubleclick into the cell with parentheses (within excel)? Does your value also change into the minus-value?

[PaigeMiller]

---

@Konkordanz wrote:

Okay, I will do it. But first: What happens if you doubleclick into the cell with parentheses (within excel)? Does your value also change into the minus-value?

---

You can try it yourself and find out.

[Konkordanz]

Youre right, sorry. I tried it and the problem is the following:

In your version the result is "(112.09)" and not that, what I need: "(112,09)" - with a comma and not with a dot.

Assumption: The numx.-Format is only in Table A involved, but not in Table b. With creating the new variable (length insgesamt_sum_char $ 20;) it gets a other format. Indeed: With this format the parentheses wont disapear in the excel-table. But I need the result with a comma. And with a comma the parentheses disappears, if I doubleclick the cell in Excel. You know what I mean?

I tried to reproduced it as an example. With this code you probably can reproduce my problem:

data a;
    numvar=0.523409; output;
    numvar=112.09; output;
    format numvar numx20.2;
run;

data b;
    set a;
	insgesamt_sum_char=put(numvar,numx20.2);
	Insgesamt_sum_char2="("||strip(Insgesamt_sum_char)||")";
run;

ODS excel file=" [...] temp.xlsx" 
options(sheet_interval="none" sheet_name="Tab08" embedded_titles='Yes');

proc report data= b;
run;

ods excel close;

[PaigeMiller]

This works for me

data a;
    numvar=0.523409; output;
    numvar=112.09; output;
run;
data b;
    set a;
    length insgesamt_sum_char $ 20;
    if numvar <71 then insgesamt_sum_char="/";
    else if numvar >=71 and numvar <=119 then
    	insgesamt_sum_char="("||strip(put(numvar,numx20.2))||")";
run;
ods excel file="test.xlsx";
proc report data=b;
    columns numvar insgesamt_sum_char;
    define numvar/order=data;
run;
ods excel close;

[Konkordanz]

Really? You open the excel file, doubleclick into the cell B6 "(112,09)", deselect it and the value keeps the same?

Strange...This happens with my excel, If I use your last code:

Before I doubleclick it, the result is perfect:

Afterwards:

[PaigeMiller]

---

@Konkordanz wrote:

Really? You open the excel file, doubleclick into the cell B6 "(112,09)", deselect it and the value keeps the same?

Strange...This happens with my excel, If I use your last code:

Before I doubleclick it, the result is perfect:

Afterwards:

---

The only thing I can say is that the whole idea of displaying numbers as character strings makes me wince. If it can't be done via a format, then I almost always think it is a bad idea, that causes a lot of extra effort (and in my opinion wasted effort) compared to leaving the number as a number. In general, this is something I avoid like the plague.

[ballardw]

---

Afterwards I export the result into a excel-file.

This works pretty well and I get the correct results in a nice file (and it wouldnt work, if the column-format would be numeric). But nevertheless I have a problem:

I open the exported excel-file and click into a Cell with a parenthesized value. By unselecting that cell, the parenthesized value changed into a minus value. For example:

The correct value is (1,0). By clicking and unselecting that cell it changed to -1 (parentheses vanished).

Why is that happen? Do you know a way, to avoid this?

---

Reason: Excel (or the programmers at Microsoft)  knows what you want better than you do. EVERY cell is a spreadsheet is subject to interpretation. Example: in an unformatted cell. Type 5-1 and hit enter. What do you see as a result? Typically I get "May-1" and is a date in the current year. In your case, Excell has been programmed to treat something entered a (<some number>) as a negative number as that is what early spreadsheets, i.e. done by hand on paper, used instead of - signs for negative values.

About the only way to prevent such behavior is to make sure that either Excel knows the cell is character, such as making sure there is single quote at the start of the value.

Perhaps

Insgesamt_sum_char=cats("'(",Insgesamt_sum_char,")";

The CATS function removes leading and trailing blanks and then concatenates the values

[Cynthia_sas]

Hi:

  If you use a style override to tell Excel an explicit format of TEXT, then ODS EXCEL will treat the cell as you want. See this example using TAGATTR as the style override:

  There's no need to pad the cell with a leading quote.

Cynthia

[Konkordanz]

@Cynthia_sas: Your hink was helpful to find the solution for my data. It didnt work with {tagattr="Format:Text"}.But it worked with: {tagattr="Format:@"}

I didnt know this command yet. Thanks!