RE: st: RE: replacing var if it meets criteria

[Nick Cox <n.j.cox@durham.ac.uk>]

This assumes that -totalFixtureCargoSize- is never missing. 

egen maxTotalFixtureCargoSize = max(totalFixtureCargoSize), by(LoadCountry) 
gen MaxPort = LoadPort if maxTotalFixtureCargoSize == totalFixtureCargoSize 
bysort LoadCountry (MaxPort) : replace LoadPort = MaxPort[_N] if missing(LoadPort) 

is a longer way round. That assumes no ties for maximum within each country. 

Nick 
n.j.cox@durham.ac.uk 


-----Original Message-----
From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Nick Cox
Sent: 31 October 2011 17:39
To: 'statalist@hsphsun2.harvard.edu'
Subject: RE: st: RE: replacing var if it meets criteria

I was pointing in a direction that is not completely wrong but would be needlessly indirect. 

(I am afraid references to MS Excel procedures don't help me personally.) 

Getting at the maximum is indeed the lesser deal here. Try 

bysort LoadCountry (totalFixtureCargoSize) : gen MaxPort = LoadPort[_N]  
replace LoadPort = MaxPort if missing(LoadPort) 

For a tutorial on technique see 

SJ-2-1  pr0004  . . . . . . . . . . Speaking Stata:  How to move step by: step
        . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .  N. J. Cox
        Q1/02   SJ 2(1):86--102                                  (no commands)
        explains the use of the by varlist : construct to tackle
        a variety of problems with group structure, ranging from
        simple calculations for each of several groups to more
        advanced manipulations that use the built-in _n and _N

Nick 
n.j.cox@durham.ac.uk 

Parker, Sophie

I've taken a look at egen but it's not exactly the mode or max I'm after exactly (the mode of LoadPort won't necessarily equal the max(totalFixtureCargoSize) and max gives me a numerical value). It's more of a SQL query, once I create the max variable:
egen max_portvol = max(totalFixtureCargoSize) if LoadPort != "", by(LoadCountry) 

I want to perform a lookup of max_portvol on totalFixtureCargoSize by LoadCountry and tell it to give me the corresponding LoadPort, i.e., Arzew in the example below and let the missing value equal this (the standard lookup routine in Excel). 

Thanks for the help. 
________________________________________
From: owner-statalist@hsphsun2.harvard.edu [owner-statalist@hsphsun2.harvard.edu] on behalf of Nick Cox [njcoxstata@gmail.com]
Sent: Sunday, October 30, 2011 5:10 PM
To: statalist@hsphsun2.harvard.edu
Subject: Re: st: RE: replacing var if it meets criteria

See this posting from two days ago on automating the mode

http://www.stata.com/statalist/archive/2011-10/msg01387.html

Or it may be that you want the maximum. See the help for -egen- either
way. My answer to your last question also advised to look at -help
egen-.

Nick

On Sun, Oct 30, 2011 at 4:32 PM, Parker, Sophie
<sophia.parker.09@ucl.ac.uk> wrote:

> I'm trying to automate the task of having to evaluate a table. For example, I have a bunch of countries with different port names, some of which are missing. I want to replace the missing value with the port that has the highest volume (volume is given by variable totalFixtureCargoSize). In the table below, this would be Arzew:
>
>
>  tabulate LoadPort totalFixtureCargoSize if LoadCountry == "ALGERIA"
>
>
>                      |      totalFixtureCargoSize
>
>            Load Port |    400000    2310000   6.08e+07 |     Total
>
> ----------------------+---------------------------------+----------
>
>                ARZEW |         0          0        380 |       380
>
>               BEJAIA |         5          0          0 |         5
>
>               SKIKDA |         0         27          0 |        27
>
> ----------------------+---------------------------------+----------
>
>                Total |         5         27        380 |       412
>
>
> I.e., if ALGERIA has a missing LoadPort, then it puts in Arzew. This command clearly doesn't work but should give you an idea:
>
> bysort LoadCountry: replace LoadPort = LoadPort if LoadPort = "" & LoadPort = max(totalFixtureCargoSize)
>

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/