Announcement

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

  • What is Excel's =COUNTIF() equivalent on STATA?

    Good morning,

    So here is my issue.
    I have two columns of raw data (Col1 and Col2 in the image below). I want to generate with STATA a third and fourth column (c1 and c2 in the image below) that basically tells me how many times has the corresponding letter appeared before. It's easier to understand with and example and an image so, for example, in C16 (c1), I want to know how many times has A16 (J) appeared before in either Col1 or Col2 but not after. This is the Excel formula I used to achieve this =COUNTIF($A$1:A15,A16)+COUNTIF($B$1:B15,A16). Does this have an equivalent on STATA?

    Here is the image of my scenario:



    Thanks

  • #2
    https://www.statalist.org/forums/for...untif-function may help.

    Comment


    • #3
      Not really, what I'm trying to do is accumulative (every row depends on herself and all the others before) while that post is generating a new column in which every row depends only on that same row.

      Thanks for your answer.

      Any other ideas?

      Comment


      • #4
        Please show a Stata data example. https://www.statalist.org/forums/help#stata 12.2 and 12.5 explains.

        Comment


        • #5
          I want an extra column that gives me an integer for every row, representing the number of times the string on that same row appears on previous rows. That's, in words, what I want.

          I know you don't like pictures here but it's crystal clear, another way of understanding, and probably the best way, is to read the Excel formula I stated above, which that does exactly what I want, it's a 2 parameter formula, easy to understand.

          I'm sure it can be done with STATA, it's a language problem, if you don't know, don't feel forced to reply and send me some rules, I've clearly stated what I want, just let someone else answer if you have no clue.

          Thanks

          Comment


          • #6
            Simplifying the question:

            Let column A be a string column.

            I want integers in column B to count how many times string in column A have appeared until that point.

            For example: For cell B14, I want to know how many times does string A14 appear on the interval A1:A13. As shown in the picture below:

            Comment


            • #7
              Cross-posted at https://stackoverflow.com/questions/...alent-on-stata

              On #5 I leave others to judge. That's not a good way to charm anyone into answering your question.
              Last edited by Nick Cox; 13 Jun 2020, 10:37.

              Comment


              • #8
                I do share Nick's reply, especially on tone and manner, which sound a bit outside the elegant style this list has accustomed us to.
                Talking straight, finding a solution to the posted issue is original poster's business only (who does not seem to have any clue, too).
                Kind regards,
                Carlo
                (Stata 19.0)

                Comment


                • #9
                  Please, don't feel forced to answer if u have no idea, now others won't come and read because you filled the post with empty replies, thanks a lot. It's a fairly easy question for anybody who knows a minimum of STATA and now I won't be getting a reply, thanks again.

                  Comment


                  • #10
                    It is easier to describe the solution by giving sample code - there is no magic single-command solution that you seem to imagine, and who wants to write an essay about code instead of writing code?

                    It is easier to write code when there is data to test it on, but since even Stata 16.1 lacks the "import picture" command, we ask for sample data using Stata's dataex command.

                    It's more rewarding to spend our time on questions that show respect for our time.

                    So don't blame this post for any lack of response to your question. Maybe somebody else will be glad to type in sample data and answer your question.
                    Last edited by William Lisowski; 13 Jun 2020, 10:54.

                    Comment


                    • #11
                      You guys feel in the need of writting responses when have absolutely no idea about the issue: rules, images, question format, manners, data missing,... all excuses. It's plain simple, if you don't have an answer you skip, nobody will think you are less inteligent or anything like that, just skip it. If you know a solution state it, if not, skip. That's what this place is all about. You just inventing excuses to justify your lack of answers towards an easy task for someone who knows STATA language minimally.

                      Btw, both Excel screenshots are not the real ones (obviously) and were generated by me in literally 20 seconds before typing my post. You have the formula written so it would take you 3 seconds instead of 20, Ctrl C, Ctrl V, just another lame excuse to jsutify lasck of knowledge.

                      Thanks again for void messages.

                      If someone has something to say respect my issue, all help is appreciated, thanks

                      Comment


                      • #12
                        You manage all at once to be self-centred, offensive and incorrect. You miss the point completely. On Statalist we are not your servants. Good questions help us help you and are much more likely to be interesting and useful to others We give and explain advice (not rules) about what to present in your own best interests.

                        Explaining what Statalist is all about to people with a combined experience of about 30 years on this list and a combined total of about 40,000 posts since 2014 alone (and more earlier) is some kind of record in juvenile presumption. We don't expect deference, but we do understand Statalist better than you do.

                        I had a look at Stack Overflow where you posted a clone of this question, There your track record shows that other people there, nothing to do with Stata, repeatedly gave you the same advice about posting better questions -- in other languages -- and made the same point about showing text, not images.

                        It's hard to feel inclined to help someone who insults you repeatedly, but just as a refutation of your implication that we are clueless, here is some code

                        Code:
                        gen long id = _n
                        bysort whatever (id) : gen wanted = _n - 1
                        sort id
                        which you would need to adapt to the variable names you use, or will use, in Stata, and which you don't show us.

                        So your insults got you somewhere by goading me into showing that I know an answer, but your future in this forum is limited unless you treat others with respect.

                        As it happens, I know almost nothing about Excel and a good solution has nothing to do with seeking a function that behaves like a function in Excel.

                        Comment


                        • #13
                          Thanks Nick!

                          I really don't know how to ask questions better, and I got mad because of getting formality issues as responses instead of plain answers to what I considered an easy question. Sorry about that.

                          I'm not sure about what you mean by showing a STATA data example, I'm not sure it's clear to me enough on how to post questions next time but anyways, I'll try my best.

                          Originally posted by Nick Cox View Post
                          Please show a Stata data example. https://www.statalist.org/forums/help#stata 12.2 and 12.5 explains.

                          Comment


                          • #14
                            If I'm finding further problems with my initial issue, should I state them here or open a new topic?

                            Comment


                            • #15
                              If I'm finding further problems with my initial issue, should I state them here or open a new topic?
                              If it relates to the initial topic of the thread (which it sounds like it does), then you can state the issue(s) here. If it's an issue completely unrelated to the original topic, then it's preferable to start a new thread. Hope this helps.

                              Comment

                              Working...
                              X