2 Replies Latest reply: Feb 13, 2013 4:22 AM by Daniel Safai RSS

    Create non existing observations in script

    Daniel Safai

      Hi,

       

      Each month i receive information (in excel) about salary the following way:

       

      CompanyCountrySalary
      MicrosoftUSA500
      MicrosoftIndia200
      MicrosoftRussia300
      IBMIndia200
      IBMChina300
      IBMRussia600
      AppleUSA1000
      AppleRussia300

       

      This list only shows the countries with existing salary. What i want to make in my script is a code that will give me the following:

       

      CompanyCountrySalary
      MicrosoftUSA500
      MicrosoftIndia200
      MicrosoftChina0
      MicrosoftRussia300
      IBMUSA0
      IBMIndia200
      IBMChina300
      IBMRussia600
      AppleUSA1000
      AppleIndia300
      AppleChina0
      AppleRussia0

       

      In other words i want my script to generate the non existing observation so i can make a pivot table that explicitly shows all countries regardless of whether there was a salary or not (as is the case for Apple in China and Russia).

       

      Can anyone tell me how to do this when the original data has many variables and observations. ?

       

      Kind regards,

       

      safai1234

        • Re: Create non existing observations in script
          Gysbert Wassenaar

          See attached example

            • Re: Create non existing observations in script
              Daniel Safai

              Thank you very much for your quick answer.

               

              I still have problems, but i think you know where i'm going. Perhaps i didn't gave i good enough description of my problem.

               

              Here is an exactly description.

               

              The variable "salary" is actually a variable i count. So think of it as an employee variable that contains some kind of ID. I want a pivot table that counts the number of employees in some chosen countries and reports 0 for countries with no employees. The problem is that countries without employees doesn't figure in the data.

               

              Here is an illustration:

               

              Data:

               

              Company

              CountryEmployee (ID number)
              MicrosoftUSA232
              MicrosoftUSA568
              MicrosoftUSA115
              MicrosoftRussia8787
              MicrosoftRussia9898
              MicrosoftRussia5656
              MicrosoftRussia6568
              MicrosoftIndia111111
              IBMUSA989
              IBMUSA854
              IBMChina65
              IBMChina23
              IBMChina54
              IBMIndia222222

               

              Pivot table definition:

               

              count(

              {<

              Company = {Microsoft, IBM},

              Country = {USA, Russia, China}

              >}

              Employee (ID number))

               

              Desired output:

               

              Company

              CountryTotal
              MicrosoftUSA3
              Russia4
              China0
              IBMUSA2
              Russia0
              China3

               

              How do i get China and Russia in the pivot table when they aren't in the data?

               

              Kind regards,

               

              safai1234