Renaming variables without space (underscore) using renvars

[Pranav Garg]

Dear Community,

I am struggling with renaming my variables using the renvars command in STATA.
Upon importing the excel data, I am using renvars to map the first row names to my data set. However, since the first row contains a lot of spaces, the entire names are not being imported, with spaces being replaced as "_" (underscores). I am trying to find a command such that STATA maps the first row observations as variable names, but excludes all numbers and spaces to only select the alphabets.
I do not want to manually change the names in the excel file and would highly appreciate a solution using STATA alone.

My imported dataset looks like this:

The code I run is:

import excel "$input/filename.xlsx", sheet("ASSETS") allstring clear
drop in 1/6
renvars, map(strtoname(@[1]))

And the result I get is:

I would like to know of a method to map the first row as variable names without the spaces.

Regards,
Pranav

[Joseph Coveney]

I can't read your screenshots, and so I cannot tell what you're showing, but can't you just use the cellrange(A7) firstrow options to import excel?

[Nick Cox]

Joseph gives excellent advice as always, but I don't think it will solve your main problem.

I quote from a reply to one of your previous threads:

Please review the Statalist FAQ

https://www.statalist.org/forums/help#stata

which is explicit that screenshots are not as helpful as you hope and that you should explain where user-written programs come from.

While visiting the FAQ, please note (#18) that the standard spelling is Stata, not STATA.

renvars is a community-contributed program, as you are asked to explain:

. search renvars, historical

Search of official help files, FAQs, Examples, SJs, and STBs

SJ-5-4 dm88_1 . . . . . . . . . . . . . . . . . Software update for renvars
(help renvars if installed) . . . . . . . . . N. J. Cox and J. Weesie
Q4/05 SJ 5(4):607
trimend() option added and help file updated

STB-60 dm88 . . . . . . . . Renaming variables, multiply and systematically
(help renvars if installed) . . . . . . . . . N. J. Cox and J. Weesie
3/01 pp.4--6; STB Reprints Vol 10, pp.41--44
renames variables by changing prefixes, postfixes, substrings,
or as specified by a user supplied rule

So, it goes back to 2001.

I can't read your screenshots either -- except very dimly -- but I think the main issue is just that most of your column headers are lousy (candidates for) Stata variable names. They rely -- as spreadsheet headers often do -- on spreadsheet users knowing the subject and being able to glance left and right to see what is in adjacent columns. strtoname() does the best it can, but it is dependent on what is supplied.

I can't see any automated way of improving your variable names except a writing a .do file with individual or grouped rename statements producing intelligent, intelligible names chosen by a person -- which at least you will then be able to use with any other datasets with exactly the same structure.

[daniel klein]

I would like to know of a method to map the first row as variable names without the spaces.

Here are the basics

unab oldnames : *
foreach old of local oldnames {
    local new = subinstr(`old'[1], " ", "", .)
    local newnames `newnames' `new'
}

rename (`oldnames') (`newnames')

As Nick has pointed out, I doubt this will lead to useful variable names. Obviously, you can add more string functions inside the loop to get closer to useful names.

Best
Daniel

[Nick Cox]

For completeness I will spell out what the code in #1 does with a self-contained example:

clear 
input str4 (x y z)
"frog" "toad" "newt" 
1  2 3 
end 

renvars x y z, map(strtoname(@[1]))  

describe 

Contains data
  obs:             2                          
 vars:             3                          
 size:            24                          
--------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------------
frog            str4    %9s                   
toad            str4    %9s                   
newt            str4    %9s                   
--------------------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

renvars automatically loops over the variables named. Here it grabs the value in the first observation of each variable and uses it as the new variable name. In my example the values frog toad newt are already suitable variable names, but strtoname() stretches the capability. What that function can't do is be smart on your behalf and choose new names that appeal to the researcher.

renvars long predates what rename groups now offers -- and in a few respects, as here, it offers more.

[Pranav Garg]

Dear Joseph,
Thank you for your advice. I am following the same, and manually renaming the variables into intelligible variable names.

Dear Nick,
I shall keep in mind the note on screenshots and user-written programs. renvars has previously done wonders for me, and I wanted to know if there was a condition using the map option in renvars that would allow me to name my variables without having to do so manually.
Thank you for your inputs, as always.

Dear Daniel,
Thank you for your command. I run into a syntax error that I am unable to work around (through no fault in your code, but mainly in the restriction of my screenshot to show my data properly). I shall proceed with manually correcting the names, as suggested by Nick.


Best,
Pranav

[Nick Cox]

As your question in #1 implied at the outset, renvars can create new variable names; the issue is whether you like the results. So, I am still not sure what your question is.