I use SAS to produce .csv files containing output from statistical
analyses.
The files have a number in one cell, and the standard error in
parentheses below.
Example:
Heigt,Weight
10,20
(1.4),(3.4)

When I import, Excel takes the numbers in parentheses to be negative,
and displays them as such.

I'd like to import them seamlessly, that is to get the numbers that
are in parantheses imported as regular numbers with a custom format
option of () around them.

Id also like the blank numbers which currently appear as (    .) to
appears simply as an empty cell.

This can't be done with a very simple VB macro, because the locations
of the #'s in parenthes change from import file to import file.

I've posted an example at web.mit.edu/cole/www/nov3.csv and the output
I'd like to have produced at web.mit.edu/cole/www/nov3.xls

(I'd like it to be as easy to do as possible, so if it can be a button
on the excel toolbar, which I press, it gives me the "find a file"
thing, i find the .csv using explorer, and it imports translates, and
saves file as same name but with .xls, leaving the .xls file open,
I'll happily pay $10)

---

Clarification of Question by fuciek-ga on 05 Nov 2002 08:53 PST

rac-ga, I did as you suggested, pasted the macro into VB editor, and
ran it, and it gave me a message saying "Processing is over" and saved
the file as sample1.xls...however, it didn't do what it was supposed
to do...
Could you download the file web.mit.edu/cole/www/nov3.csv2
(i changed the extension so taht if you are using explorer it won't
automatically convert itinto .csv)

My sas program does indeed spit out the () correctly (though the file
I posted to the web I think was wrong, because i had opened and closed
it in Excel, so it had already done the conversion).

Could you perhaps send to mhapt@yahoo.com an excel sheet with the
macro, the sample file I posted, and the output your macro generates,
so taht I can try to replicate it?

If we get this sorted out I'll pay $10, even if it is not a "button"..

thanks much,
Shawn

---

Clarification of Question by fuciek-ga on 06 Nov 2002 11:08 PST

OK, it worked this time, thanks very much, I'm not sure why it didn't
work last time.
I've done this once before, but I think you have to enter "answer"
before I can tell Google to pay you...is that correct?
Shawn

how about using a little program to just remove all of the parentheses
from the .csv file before importing it into Excel?

I looked at your .csv file and it doesn't contain any values in
parentheses.  What it contains is negative numbers.  Here is clear
text of the first three lines:

Variable,esti01,esti02,esti03,esti04,esti05,esti06
Intercept,0.163596389,-0.023216279,0.000711402,0.044652934,-0.007882827,-0.000212302
,-0.035514101,-0.027027699,-0.032128626,-0.031581957,-0.027900044,-0.026563415

So you are not having (0.035514101) misinterpeted as a negative
number....it is a negative number.  Your SAS routine isn't outputting
what you think it is.

If I understand your question correctly, your problem is that Excel
decides that the values surrounded by parentheses are negative numbers

One way around this problem is to first rename your .csv file to a
.txt file,
then when you open it with Excel it will let you import it as a comma
delimited file, but instead of letting the columns be "general" format
change them to "text" and the values that have parentheses will stay
as such.

Now in regards to the (           .) values you could do a search and
replace for each one of those replacing it with (0.0)

Another way to fix the way Excel displays negative numbers is to
highlight the whole row, for example Row 5 and then under the Format
menu choose Cells and tell the cells to be a number with 5 decimal
places with () around negative numbers.

I hope this helps... just in case a researcher doesn’t take up the
challenge of making a script for you.

In regards to the .csv file you posted. it is true that there are no
values with () except the ones with just (         .)

In making my previous comment I assumed that you had just saved the
.csv file from Excel and therefore destroying your original file.

Here is a start.  Assuming the .csv is correct, and the sheet in excel
is named "nov3", AND that the first column in the standard error row
is BLANK, run this macro;

Sub mr_fixit()
    For Each rw In Worksheets("nov3").Rows
        If Range("a" + Trim(Str(rw.Row))).Value = "" Then
            Rows(Trim(Str(rw.Row)) + ":" + Trim(Str(rw.Row))).Select
            Selection.NumberFormat = "0.00000_);(0.00000)"
        End If
    Next rw
End Sub

Hi,
 I will try to write a vba macro as needed by you be End of day.

RAC

Oh, I forgot about the (    .) to be replaced by blanks.  Here is an
updated macro which does the replacement for you.

Sub mr_fixit() 
    For Each rw In Worksheets("nov3").Rows 
        If Range("a" + Trim(Str(rw.Row))).Value = "" Then 
            Rows(Trim(Str(rw.Row)) + ":" + Trim(Str(rw.Row))).Select 
            Selection.NumberFormat = "0.00000_);(0.00000)" 
        End If 
    Next rw 
    Cells.Replace What:="(           .)", Replacement:="",
LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
        ReplaceFormat:=False
End Sub

It's not as all inclusive as you wanted, but I hope this helps.

Hi,
Try the following macro which will work as you want.
Sub FormatCSV()
' Macro recorded 11/5/02 by RAC
'Select file to open
    fileToOpen = Application.GetOpenFilename("Text Files (*.csv), *.csv")
    If fileToOpen = False Then
        MsgBox "No File selected. Try again"
        Exit Sub
    End If
    
'Open the file
    Workbooks.OpenText FileName:=fileToOpen, Origin:=xlWindows, _
        StartRow:=1, DataType:=xlDelimited, Comma:=True
'Count the no of columns
    Set acell = Range("A1")
    colcount = 0
    Do While acell.Value <> ""
        colcount = colcount + 1
        Set acell = acell.Offset(0, 1)
    Loop
        
'Loop each row
    For Each rw In Worksheets(1).Rows
        If Range("B" & (rw.Row)).Value = "" Then
            Exit For
        End If
        If Range("a" & (rw.Row)).Value = "" Then
            Set acell = Range("B" & (rw.Row))
            Count = 2
            Do While Count <= colcount
                If Right(acell.Value, 2) = ".)" Then
                    acell.Value = ""
                Else
                    acell.Value = Abs(acell.Value)
                    acell.NumberFormat = "(General)"
                End If
                Count = Count + 1
                Set acell = acell.Offset(0, 1)
            Loop
        End If
    Next rw
    
'Get save file Name
savefile:
    Do
        saveFileName = Application.GetSaveAsFilename("Sample1.xls")
    Loop Until saveFileName <> False
    On Error GoTo savefile
    ActiveWorkbook.SaveAs FileName:=saveFileName, FileFormat:=xlNormal
 
    MsgBox "Processing Over"
        
End Sub


How to Run: Copy  this macro. Open a new Excel file.  
In the tools menu --Macro--Visual Basic Editor.  
Paste the macro in the VB editor screen. 
Save it.  To run it Open the excel file.
from menuTools-->Macro-->Macros-->Select 
the macro name and Select Run.   

It will show a file open dialog box. It put the csv file to open
After processing will show a file save as dialog box. Give save as filename.
After saving as xls file will show a msg box "processing over"

If you need any further clarification Please let me know.

Thanks,
RAC

Hi,
   Can you explain in detail what is not working? What is the error?
It will be helpful to analyse the problem better.
For your referance I posted the following 3 files in the website.
1. http://rac-ga.tripod.com/nov3.xls
This file contains only the macro formatCSV(). This xls sheet contains
one tool bar butten(yellow face icon with tool tip "File Update". So
by pressing the button you can run the macro.
2. http://rac-ga.tripod.com/nov32.csv
This file contains the csv file downloaded from your site.
3.http://rac-ga.tripod.com/sample3.xls
Processed output saved as xls file.

Thanks,
RAC

Hi Shawn,
      Nice to note that the macro is working fine and helped in
solving your problem. As of now, I am not a paid researcher of Google,
So forget about fees.

Thanks,
RAC