Counting number of times an ID value appears in a list of variables

[Daniel Jang]

Hello,

I am trying to count the number of times a certain respondent is mentioned by another respondent as a friend using ego-centric data. Also, I should mention that due to specific circumstances, I am unable to download specific stata packages.

Currently, my data is formatted as follows (on a much larger scale):
ID Friend 1 Friend 2 Friend 3
1 2 3 4
2 3 5 1
3 4 2 1
4 1 4 2
5 1 3 2

My goal is to count the total amount of times an ID number was listed among all friend variables and give it to the respective ID row:

ID Friend 1 Friend 2 Friend 3 Count
1 2 3 4 4
2 3 5 1 4
3 4 2 1 3
4 1 4 2 3
5 1 3 2 1

Any help would be appreciated on how to code this.

Thank you for your time!
Daniel

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id friend1 friend2 friend3)
1 2 3 4
2 3 5 1
3 4 2 1
4 1 4 2
5 1 3 2
end

isid id, sort
levelsof id, local(ids)
reshape long friend, i(id)

//    ELIMINATE SELF-REFERENCES AS FRIENDS
drop if friend == id
//    ELIMINATE ANY DPLICATE REFERENCES AS FRIENDS
duplicates drop

//    COUNT UP FRIEND REFERENCES
gen friend_count = .
foreach i of local ids {
    count if friend == `i'
    replace friend_count = r(N) if id == `i'
}

reshape wide

Notes: id 4 has listed him/herself as a friend. I assume this is not really permissible, so in the code I have excluded self-references. And, although there are no instances of this in the example data shown, I also assume it is not permissible for an id to list the same person more than once as a friend. So the code will eliminate such redundancies if it encounters any. Finally, as per your restrictions on installing packages, this code uses only official Stata commands.

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Daniel Jang]

Hi Clyde,

I appreciate you help on this. I ran the code you suggested and seem to be running into a problem. The code runs without an error message, but the friend_count variable only has missing cases even after running the foreach command. Would you happen to know any reason why this may be the case?

[Clyde Schechter]

I think you are running the code incorrectly. I tested this code before posting it, and it definitely doesn't leave the variable friend_count empty.

Probably what's happening is that you are trying to run the code line-by-line or in small sections. Because the code uses local macros, you can't do that. When you interrupt the code execution, any local macros that were defined in what you ran disappear and they are then unavailable in the next line or section you try. So, for example, if the code is interrupted somewhere between the -levelsof- command and the -foreach- loop, local macro ids will not exist when the -foreach- is run. Non-existent local macros are interpreted as empty strings. So -foreach- will think that is being asked to repeat the code over no values of id at all, and will, therefore, do nothing. That would leave all the values of friend_count as missing.

Run the entire thing in one fell swoop, without interruption from beginning to end. I'm pretty sure that will solve your problem.

In the event it does not, please post back with new example data that reproduces this difficulty and I will troubleshoot it. Please be sure to use the -dataex- command if and when you do that.

[Daniel Jang]

Clyde,

Not running all the code together was definitely the problem. I was working under the assumption that local macros were saved across commands, but I was incorrect. Thanks you so much for your help.