Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Can Stata substitute Excel for IF,AND, OR functions ?

    For years I have been frustrated with the limitations of Excel.
    I need to perform 'IF, AND, OR' etc formulas down 200,000+ rows by 10 columns wide, all cells in that range
    contain formulas. Excel can accommodate around 40,000 rows making that 400,000 cells of formulas in an
    acceptable time period. I would run about 20-30 simulations in the space of one hour.
    When using Excel and rows of 70k + the CPU would run to 100%
    I would like to know if Stata can carry out the above as I purchased version 10 about three years ago and it is
    currently being unused. I was a user of Stata a few years ago but on pre programmed code. I am not a programmer
    but willing to learn the basics required to perform these tasks.
    The computer spec is as follows: Dell 7500 Inspiron Workstation with memory riser 24GB Ram and 8 processors.
    Hard drive 450GB with 330GB free. I would consider increasing the RAM to say 96GB+ if it would speed things up.
    Any advice you can give would be much appreciated.
    Thank You
    Ian

  • #2
    Can you give an example of what exactly you mean by

    I need to perform 'IF, AND, OR' etc formulas down 200,000+ rows by 10 columns wide,
    What would such an operation be? What would be supposed to do?

    Best
    Daniel

    Comment


    • #3
      Best way is to attach a spreadsheet so you can see the formulas in question.
      Attached Files
      Last edited by Ian Bell; 26 Sep 2015, 05:07.

      Comment


      • #4
        Ian, attaching a spreadsheet is not the best way. (Many Forum readers either do not use spreadsheet products or are unwilling to download spreadsheet attachments. Please read the Forum FAQ for advice on how to post questions in order to maximize the chances of getting helpful replies.) Please answer Daniel Klein's answer directly. One way of doing so would be to post a snippet of data (using CODE delimiters please, as the FAQ recommends) accompanied by text explaining exactly what you want to do. At the same time, I recommend that you look at the help files and manuals for the if command and the if qualifier (and note the distinction between them). Perhaps also look at simulate (as you mention simulation). Referring to the help files and manuals in your rephrased question would be useful. I am confident that there is a straightforward Stata solution to what you want to do.

        Comment


        • #5
          From a very quick glance I would guess you are looking for some simple generate or egen calls to create what you are looking for. It also seems at least about half of the IF statements will not be necessary in Stata, since these statements basically boild down to tell Excel to return a missing value for the calculation if the input is a missing value. This will be default behavior of Stata under most circumstances.

          By the way, your sheet throws back Err:504 for virtually all of your formulas, so I will pick one example that does not to illustrate the Stata equivalent.

          Your code for column prep6 is

          Code:
          IF(A4="";"";1/J4)
          In Stata this would be something like

          Code:
          generate prep6 = 1/J if !mi(A)
          given you have all numerical information stored in numerical variables. Well, the 1-to-1 translation would be

          Code:
          generate prep6 = cond(mi(A), ., 1/J)
          but the result will be the same.

          Best
          Daniel
          Last edited by daniel klein; 26 Sep 2015, 06:02.

          Comment


          • #6
            Along with the good advice above, let me take a step away from your immediate question to offer the following, following up on Stephen's advice and based on your comment that you used Stata only with pre-programmed code.

            When I began using Stata in a serious way last fall, I started by reading my way through the Getting Started with Stata manual relevant to my setup. (Much of which will be familiar to you from your previous, if limited, use of Stata.) Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through Stata's Help menu. The Stata documentation is unusually comprehensive and well repays the effort spent studying it. And let me mention this: if any of your data involves dates and times, be certain to read the chapter in the User's Guide on working with Stata dates and times before starting in on that work.

            With that said, using release 10 might pose a bit of a challenge, since Stata is now at version 14. When seeking advice from sources like Statalist, you're likely to get some advice that just isn't supported until a later release than 10. Be sure to continue to mention your version, as you did in this post. There are folk here who (a) have been working with Stata forever and (b) can remember the differences between earlier versions. If you find Stata useful, you might consider following the advice Marcos offered in response to one of your earlier posts and consider upgrading to the current version.

            Comment


            • #7
              With that said, using release 10 might pose a bit of a challenge
              Almost ironically this is especially true for the (good) advice given by William himself, as the documentation in pdf was introduced with Stata 11, thus, using Stata 10 you do not have full access to this wonderful resources, unless you have the manual in printed form with your purchase of Stata. Note, however, that a lot of the manual (for Stata 13 and 14 at least) is available online.

              Best
              Daniel

              Comment


              • #8
                I can only offer my humblest apologies to Stephen, Daniel and William for my lack of responses. The following day after my post I travelled for two and a half months and to be brutally honest I completely forgot about the question I posted on here. I have since moved on from there with my project and would like to post a new question if you gentlemen are still willing to oblige but first I will read conditions about sending attachments. Once again apologies to you guys.
                Best
                Ian

                Comment

                Working...
                X