Create an id when merging data

[Thomas Klein]

Hey Guys,

I would like to merge Stata files. The observations I would like to merge have not an id yet. My Intention is to allocate an id to each Observation which is composed of the year, the id of the house and the id of the Refrigerator.

I have the variables year, houseid, refrigeratorid and now I would like to create a variable id [year][houseid][refrigeratorid]

With the command generate I tried to do that but regrettably this does not work:

gen id = "year""houseid""refrigeratorid" but stata does not recognize that year is a variable and therefore gives the variable always the value "year".


Moreover I wonder whether there is a command in that which creates new Folders. If I want stata to save a file or to access a path which does not exist yet, Stata aborts running through my dofile.

Could somebody please help me?

Thank you very much in advance.

Thomas

[Charlie Joyez]

I would like to create a variable id [year][houseid][refrigeratorid]

Generally speaking I'd recomand you to proceed with the following:

egen newid = group(year houseid refrigeratorid)

BUT : If you want to have the same id for the same year / house /refrigerator from two different dataset, the previous command wont work.

You can concatenate variables as you tried to do but let me remind two things.
-Concatenation only works with string variables, so you probably will have to use a tostring command for the year variable (and maybe for the two others.)
-The command is

gen newid=string_year+string_houseid+string_refrigeratorid

where string_var is the variable coded in string format.

Charlie

[Nick Cox]

What you say you typed is just illegal, so I wonder what you really typed. Double quotes " " are used in Stata for specifying literal strings, and only for that purpose.

You can concatenate identifers using egen, concat().

Watch out for ambiguities. You don't say anything about your house and refrigerator identifiers, but any concatenation must be reversible. Using separators is a way to ensure that.

[Nick Cox]

Charlie's comment that "concatenation only works for string variables" is certainly correct in spirit, but some footnotes are possible.

egen, concat() was written as a convenience egen function to allow numeric variables to be concatenated too. It doesn't violate that principle as string() is used internally when needed.

More generally, tostring is itself a convenience command. It was made public originally because destring was public and people were asking for its inverse. (I'd written a rudimentary tostring as a programming exercise.) That request was a bit of a puzzle as string() already existed but tostring was made public too. Now tostring is more general (e.g. in allowing you to work on several numeric variables at once, which would otherwise require a loop). But I see unnecessary uses of tostring too. For example

gen funnydate = string(year) + string(month, "%02.0f") + string(day, "%02.0f")

would be one way to get string dates such as "20150330". For that kind of problem string() is much more direct than tostring. That might be true here too, but Thomas doesn't tell us anything about variable types.

[Thomas Klein]

Sorry, I did not clarify enough that I don't want Stata do create a "randomn" variable but where you can already read in the id which year the Refrigerator is from

To illustrate which Id I desire, I provide an example:

year	houseid	refrigeratorid	newid
2000	101	3	20001013
2001	101	4	20001014
2002	103	2	20001032

[Charlie Joyez]

We get that point, this is why instead of the convenient group command, I recomanded you to concatenate the three other variables.
Nick also gave you an elegant concatenation function, that will do what you want.

[Nick Cox]

There is a mkdir command in Stata.

[Thomas Klein]

ok, thank you
concerning the variable Format and type:
the bad Thing is that the Format and the type of the variables vary. the data has been imported from Excel
i have found those dataformats and types:

long	int	str5	str6	str4	str3
%10.0g	%10.0g	%9s	%9s	%9s	%9s

[Nick Cox]

Thomas: You have not said which of your key variables is of which type. But you should have enough information to solve your problem now.

[Thomas Klein]

thank you, egen, concat()as well as mkdir works. As Nick mentioned above one has to pay Attention to ambiguities with regard to the new id
when merging some files I got this Problem and now I would like Stata to Show me These observations where the id is the same so that I get an idea why there are ambigous/equal ids
is there a function showing the identical ids in Stata
for instance, with".list" one can only set criteria concerning the variables values but cannot enter an info that stata should only list obs with identical ids
I'd appreciate if you can help me another time

[Charlie Joyez]

It exists : -duplicates report -, or -duplicates list- (-help duplicates- will help you to understand the differences).

[Thomas Klein]

Charlie,
thank you very much that helped me!
I am sorry for my questions but it is quite difficult for me to look for a command when I do not kno if it exists and how it can be named

I have still one question whether you can write in your dofile to save the same dofile to another path and to run this dofile in the new path
I want to create a dofile which first Downloads the data then treats it etc. In the third line one can Chose the datapath by means of local datapath
however I would like to save this dofile in this datapath in order to avoid using `datapath' every time when I Access a data file

Hoping that my explanations are clear enough I look Forward to your answers

[Charlie Joyez]

I would think that the- cd- (change directory) command is what you need (as usual see -help cd- ).
This command tells stata the directory you want to settle in, and then the use or save command already happend in this directory. It doesn't prevent you to do some sub-directories to organize your data, but you won't have to re-write the whole path.
example :

use "C:\download\temp\~~~verycomplicatedpath~~~~.dta"
cd "C:\User\Work\statastuff"
save "initialdata.dta"
some operation
save "\myresults\outcome.dta"

Then you won't have to write the datapath everytime, and your initial dataset in saved in a clear directory, easy to use.

[Thomas Klein]

cd is definitely an Option for what I intended to do, thank you!

Nick: mkdir youcreates a new path that quite cool but in case I want to run my dofile another time, Stata aborts running through the dofile since the path already exists and mkdir does not encompass an Option to overwrite or replace paths, is there maybe another command which might contribute to the solution I desire?

[Robert Picard]

The capture command can be used to prevent Stata from stopping if the directory already exists.