Creating macro containing values of two variables, across levels of one of them

[colinodden]

I'm working with person-level data grouped by a variable 'year'. Merged into the data is a year-level variable 'tfr.' My goal is to build up text in a local macro containing the value of each year and its corresponding value of 'tfr' (that's a proximate goal, not the ultimate one, but I'm interested in the solution to this problem for its own sake).

I've tried

levelsof year, local(yrs)
levelsof tfr, local (tfrs)
local n : word count `yrs'
forvalues i= 1/`n' {
    local y : word `i' of `yrs'
    local t : word `i' of 'tfrs'
    local yrtext `yrtext' "`y', `y' <TFR=`t'>"
}

However, -levelsof- returns a local sorted ascending, not in order of appearance. That means that I can't -sort year- and then run -levelsof tfr- to get the index positions of the two macros to line up.

I can imagine a number of silly ways to achieve what I want (eg -preserve-, -keep if year==`y', push an element into my tfr macro, -restore-). I'm hoping for a non-silly way. Cheers!

[Clyde Schechter]

I'm not sure I understand what you're looking for, but perhaps

egen year_tfr = concat(year tfr)
levelsof year_tfr, local(yrtext)

You might need to play with the -format- and -punct- oprtions of -egen, concat()- to get this to look exactly how you want it.

[colinodden]

Clyde -- Thank you. I appreciate the elegance of your proposed solution.

I erred toward brevity and didn't provide much context about the desired outcome. Here's a restatement of the proximate goal: produce something that looks like this "1974, 1974 <TFR=5.1>" "1980, 1980 <TFR=4.6>" and so on.

I was searching for the functional equivalent of a verboten command:

local t = tfr if year==`y'

[Clyde Schechter]

So the result of -egen, concat()- is a tad sparse compared to your desired goal. What about this:

gen year_tfr = string(year) + ", " + string(year) + " <TFR=" + string(tfr, "%2.1f") + ">"
levelsof year_tfr, local(yr_txt)

The -levelsof- command will automatically wrap compound double quotes around each level of year_tfr, so the result will be what you were looking for except that your single quotes will be compound double quotes. Hopefully that won't pose any problems when you are using local macro yr_txt.

[colinodden]

However, I don't mind taking a sparse solution and substringing to get what I want. I'd like the functional equivalent of -levelsof-, but one that builds its macro in the order in which the data are currently sorted.

[Nick Cox]

levelsof is a command, not a function. That aside, its equivalent respecting sort order is

ssc desc valuesof

[Clyde Schechter]

So maybe this:

// FIRST GET THE SORT ORDER INTO A VARIABLE
gen long seq = _n
// TAG FIRST OBSERVATION FOR EACH LEVEL OF year
by year (seq), sort: gen byte tag = (_n == 1)
// RESTORE SORT ORDER AND LOOP OVER OBSERVATIONS
isid seq, sort
local year_tfr
forvalues j = 1/`=_N' {
    if tag[`j'] == 1 {
        local year_tfr `year_tfr' `=year[`j']' `:display %2.1f tfr[`j']'
    }
}

By the way this code, as well as all my earlier examples, assumes that there is an exact 1-1 correspondence between values of year and values of tfr. If that is not true, none of the code will work, but then I would also think the problem ill-posed.

This code gives you a "sparse" solution. You can either build-in the decorations in the -local- command inside the loop, or you can do some dancing with substringing etc. after the fact.

[colinodden]

levelsof is a command, not a function.

Yes. Did one of us say otherwise?

-valuesof- does exactly what I sought. Many thanks.

[Nick Cox]

You asked for the functional equivalent. No function corresponds to levelsof, as it is a command.

[Samir Kelada]

Colin,
would you mind sharing your final solution if it uses valuesof ? That would be helpful for me.
Thanks,
Samir

[Samir Kelada]

I have a related question, which is whether one can use valuesof to specify a solution to the following problem. For the following data set, can one specify a macro that reads in a file and does some commands based on values of the original data set?

Data :
fruit site locus
apples a 1
oranges b 2
bananas c 3

clear
use data
levelsof fruit, local(levels)
foreach rep of local levels {
clear
insheet using "`apples'.txt"
gen locus_2=`locus'+1
save "`apples'_`site'.dta", replace
}

[colinodden]

First, regarding -valuesof-: It sort of does the job, although it's not a drop-in replacement (Nick didn't say as much): it doesn't return unique values, so you have to pare down the list if you want -levelsof- equivalence.

By way of demonstration:

cap noi ssc install valuesof
clear
set obs 10
gen x=1
replace x=2 in 6/10
list
valuesof x
local xvals `r(values)'
local uniqvals : list uniq xvals
di `uniqvals'
*** now in reverse!
gsort -x
list
valuesof x
local xvals `r(values)'
local uniqvals : list uniq xvals
di "`uniqvals'"

[Clyde Schechter]

Well, if you have to go through all of that, it seems far more complicated than the code in my last post from yesterday, which will give you what you want quickly, with no post-processing required. Have you tried that yet?

[colinodden]

Clyde - I've proceeded through each of your three examples and they're all functional, and a reminder of the cleverness available via flavors of -egen-. Thanks for all. I admit a preference for dealing in macros over altering data files when possible, making your last example closer to ideal (no criticism expressed or implied). I hope to present a comparison of approaches, run with timestamps (my current data are 3.7M cases X 10 vars), as soon as time permits.
For the moment, I'm favoring this:

  levelsof year, local(yrs)
  foreach y of local yrs {
     levelsof tfr if year==`y', local(t) // will only return a single value if year:tfr is 1:1, as it is for my particular case
     local yrlab `yrlab' "`y', `y' <TFR=`t'>"
  }

As you indicate for your last example, this only works if 'year' and 'tfr' are 1:1. In my particular case, they are. A scenario where they're not is exactly where an order-of-appearance function like -valuesof- could come in handy.
@Keledas - it looks like Clyde's posted a suggestion to your question in another thread: http://www.statalist.org/forums/foru...nitial-dataset
Nick -- If there's a Statalist-dialect rule such that the otherwise generic phrase "functional equivalent" only applies to functions, in a way that overrides conventional English usage, I was unaware. I mean this in good humor, and I note that in other threads you've made it clear that -levelsof- is a command rather than a function.

[Nick Cox]

I write in good humour too.

We're talking about my single sentence

levelsof is a command, not a function.

I was underlining for anyone who might be confused that we are not talking about Stata functions, but Stata commands, and there's a difference. Partly because of varying terminology elsewhere, those learning Stata sometimes use terminology that does not match Stata's terminology. Sometimes a translation is immediate; other times there is real puzzlement over what is being said or sought. Often using the wrong terms hardly matters; but it is also important for anyone wanting to understand and become proficient in Stata that they learn the right terminology, as otherwise it is just too difficult to become self-propelled and learn for yourself by studying code and documentation. People can keep thinking "columns" to themselves if they like, but they need to learn to think "variables" if working with documentation is going to be successful.

Many people ask questions and are completely focused on getting an answer to their own question. That's OK. But when active users contribute answers they tend to want to write in a way that helps as many people as possible, including all those people just reading the thread at the time and later. That's how I write most of the time. I am talking to the unseen crowd as well as the questioner.

Sorry if that seemed cryptic or unclear or you thought it was somehow pointed. It wasn't.

P.S. "functional equivalent", with nothing else said, certainly does mean "does the same kind of thing", here and elsewhere. It's the context that makes the term potentially ambiguous.