Need help (!) with making a variable that only counts some unique values of another variable, and not all. Much better explanation in post.

[Anna Frost]

So my problem is this:

I have a stringvariable with 69 unique values that tells me the position in the household (head of household, child of the head, aunt of the head, spouse of the head, servant of the head, etc. )

i also have a variable that tells me the household number for each individual.

I have already created dummy-variables for head of house, for wife of head, and for child of head.

I want to create a variable that is grouped by household, that contains all the households with a head of household, a child and a spouse ( a nuclear family if you will).


How do i create this variable making sure that families that contain more than a head, a wife and children are not counted also? And also making sure that the household contains all of their children.

So in short: I want to find out how many families ONLY contain a head of household, a wife, and their children.

I really hope that one you brilliant minds out there can help me! I only started learning Stata some months ago, and its a very steap learning curve.

[Marcos Almeida]

Welcome to the Stata Forum / Statalist,

Please read the FAQ, because it has excellent recommendations about making a very informative post.

Unfortunately, to provide an answer to your case, much effort would be made just to prepare the data according to the specific situation.

By sharing data/command/output under CODE delimiters or by using - dataex - you will probably provide much better information.

If the data is big or you are not allowed to shared it, a toy example will do fine as well.

That being said, and quite generally, it seems - egen - and - gen - commands will be a helpful strategy.

[Nick Cox]

The FAQ also explains our policy on cross-posting, which is that you are asked to tell us about it. Cross-posted at https://www.reddit.com/r/stata/comme...t_only_counts/

[Anna Frost]

Ok, I have read the FAQ, and I hope this post is sufficient:

My dataset consists of 69 variables and 372,936 observations, each a person from a census. the dataset looks something like this: ( i have put in variables that I think is the most relevant for my question.)

householdnumber	idnumber	position in household	child	head of household	spouse of head of household	aunt of head of household	servant	head_in_household	spouse_in_household
1	1	child		o	0	0	0	1	0
1	2	head		1	0	0	0	1	0
1	3	aunt		0	0	1	0	1	0
2	4	head		1	0	0	0	1	0
2	5	servant		0	0	0	1	1	0
3	6	head		1	0	0	0	1	1
3	7	child		0	0	0	0	1	1
3	8	spouse		0	1	0	0	1	1
3	9	servant		0	0	0	1	1	1

Now my question is as follows:

I have a stringvariable with 69 unique values that tells me the position in the household (head of household, child of the head, aunt of the head, spouse of the head, servant of the head, etc. )

i also have a variable that tells me the household number for each individual.

I have already created dummy-variables for head of house, for wife of head, and for child of head, as well as dummyvariables that tell me if a household contain any of these. As you can see in my database example.

I want to create a variable that is grouped by household, that contains all the households with a head of household, a child and a spouse ( a nuclear family if you will).


How do i create this variable making sure that families that contain more than a head, a wife and children are not counted also? And also making sure that the household contains all of their children.

So in short: I want to find out how many families ONLY contain a head of household, a wife, and their children.

Also, I have crossposted this to Statas subreddit: https://www.reddit.com/r/stata/comme...t_only_counts/

[Nick Cox]

FAQ Advice #12 explains about using dataex to give a data example. Your example is helpful but still requires surgery to be useful. it looks more like a copy and paste from a spreadsheet than a report from Stata. The child field is blank in your example. Perhaps you could produce some variation on this and then someone may suggest code.

clear 
input householdnumber    idnumber str7 position    head spouse aunt servant head_in_household    spouse_in_household
1    1    child        0    0    0    0    1    0
1    2    head        1    0    0    0    1    0
1    3    aunt        0    0    1    0    1    0
2    4    head        1    0    0    0    1    0
2    5    servant        0    0    0    1    1    0
3    6    head        1    0    0    0    1    1
3    7    child        0    0    0    0    1    1
3    8    spouse        0    1    0    0    1    1
3    9    servant        0    0    0    1    1    1
end

[Anna Frost]

Yes, i saw the dataex, but i was not sure if it was a command, and if so how to use it, so i thought it better to do the other thing. But alas, i have put the variables together with dataex and i hope that it maybe is clearer then?

input long(postid householdnumber) strL position_in_household_std float(spouse spouse_in_household child child_in_household head_of_house head_in_household)
1 1 "Husstandsoverhoved" 0 1 0 0 1 1
2 1 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
3 1 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
4 2 "Husstandsoverhoved" 0 1 0 1 1 1
5 2 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
6 2 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
7 2 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
8 3 "Husstandsoverhoved" 0 1 0 1 1 1
9 3 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
10 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
11 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
12 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
13 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
14 3 "Husstandsoverhoveds ægtefælles forældre" 0 1 0 1 0 1
15 3 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
16 4 "Husstandsoverhoved" 0 1 0 0 1 1
17 4 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
18 4 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
19 4 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
20 5 "Husstandsoverhoved" 0 0 0 0 1 1
21 5 "Husstandsoverhoveds særbarn" 0 0 0 0 0 1
22 5 "Husstandsoverhoveds særbarn" 0 0 0 0 0 1
23 5 "Husstandsoverhoveds logerende" 0 0 0 0 0 1
24 5 "Husstandsoverhoveds logerende" 0 0 0 0 0 1
25 5 "Husstandsoverhoveds logerende" 0 0 0 0 0 1
26 6 "Husstandsoverhoved" 0 1 0 0 1 1
27 6 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
28 6 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
29 7 "Husstandsoverhoved" 0 0 0 1 1 1
30 7 "Husstandsoverhoveds søskende" 0 0 0 1 0 1
31 7 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
32 7 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
33 7 "Husstandsoverhoveds logerende" 0 0 0 1 0 1
34 8 "Husstandsoverhoved" 0 1 0 0 1 1
35 8 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
36 8 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
37 9 "Husstandsoverhoved" 0 1 0 1 1 1
38 9 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
39 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
40 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
41 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
42 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
43 9 "Husstandsoverhoveds forældre" 0 1 0 1 0 1
44 9 "Husstandsoverhoveds søskende" 0 1 0 1 0 1
45 9 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
46 10 "Husstandsoverhoved" 0 1 0 1 1 1
47 10 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
48 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
49 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
50 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
51 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
52 10 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
53 11 "Husstandsoverhoved" 0 1 0 1 1 1
54 11 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
55 11 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
56 11 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
57 11 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
58 11 "Husstandsoverhoveds familie" 0 1 0 1 0 1
59 12 "Husstandsoverhoved" 0 0 0 1 1 1
60 12 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
61 12 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
62 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
63 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
64 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
65 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
66 13 "Husstandsoverhoved" 0 1 0 1 1 1
67 13 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
68 13 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
69 13 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
70 13 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
71 14 "Husstandsoverhoved" 0 1 0 1 1 1
72 14 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
73 14 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
74 15 "Husstandsoverhoved" 0 1 0 0 1 1
75 15 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
76 15 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
77 16 "Husstandsoverhoved" 0 1 0 1 1 1
78 16 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
79 16 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
80 16 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
81 16 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
82 16 "Husstandsoverhoveds familie" 0 1 0 1 0 1
83 16 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
84 17 "Husstandsoverhoved" 0 1 0 1 1 1
85 17 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
86 17 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
87 17 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
88 18 "Husstandsoverhoved" 0 0 0 0 1 1
89 18 "Husstandsoverhoveds plejebarn" 0 0 0 0 0 1
90 18 "Husstandsoverhoveds søskende" 0 0 0 0 0 1
91 19 "Husstandsoverhoved" 0 1 0 1 1 1
92 19 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
93 19 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
94 19 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
95 20 "Husstandsoverhoved" 0 1 0 1 1 1
96 20 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
97 20 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
98 21 "Husstandsoverhoved" 0 1 0 1 1 1
99 21 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
100 21 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
end
[/CODE]
copy up to and including the previous line --------- --------

Listed 100 out of 327936 observations
Use the count() option to list more

.

[Anna Frost]

I forgot to tell that the values are in danish, I just translated the variable names so that you guys could understand.

Sorry for all the mistakes, I guess I really am a beginner in this Stata universe...

[Weiwen Ng]

From the example you've given to date, this should work:

sort householdnumber
by householdnumber: egen nuclear_family = max(cond(spouse_in_household == 1 & child_in_household == 1 & head_in_household == 1, 1, 0))

From the way you generated the example, it looks like you've already found out about -egen ... max()-. -cond- is like the IF function in Excel, it basically means if the condition is true, then output a, else output b. Now you should be able to just combine the two.

Afterward, working with a long text variable is not very fun as you have to type the full string every time you use generate. You could have encoded that variable as a numeric one. Type

help encode
encode position_in_household, generate(position)
tab position
tab position, nolabel

[Anna Frost]

Hi Weiwen, I have not been introduced to cond before, but from what I can tell, this does not help me. The problem is of course that I have in my dataset households where the conditions ARE true, that being that there is a head of household, a wife, and their own kid(s) but some of the families also have one or more of the other values( such as an aunt living there) And what I want is to be sure that families that have more than just these three types of position_in_household does not get counted.

so I ran your code and it still counts families that consist of more than just these three conditions.

I can show you the dataex with the relevant variables that comes up after I ran your code.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(nuclear_familytest kernefamilie) long husstnr strL stilling_i_husstand_std
1 1  2 "Husstandsoverhoved"                         
1 1  2 "Husstandsoverhoveds ægtefælle"            
1 1  2 "Husstandsoverhoveds fællesbarn"            
1 1  2 "Husstandsoverhoveds logerende"              
1 1  3 "Husstandsoverhoved"                         
1 1  3 "Husstandsoverhoveds ægtefælle"            
1 1  3 "Husstandsoverhoveds fællesbarn"            
1 1  3 "Husstandsoverhoveds fællesbarn"            
1 1  3 "Husstandsoverhoveds fællesbarn"            
1 1  3 "Husstandsoverhoveds fællesbarn"            
1 1  3 "Husstandsoverhoveds ægtefælles forældre" 
1 1  3 "Husstandsoverhoveds tjenestefolk"           
1 1  9 "Husstandsoverhoved"                         
1 1  9 "Husstandsoverhoveds ægtefælle"            
1 1  9 "Husstandsoverhoveds fællesbarn"            
1 1  9 "Husstandsoverhoveds fællesbarn"            
1 1  9 "Husstandsoverhoveds fællesbarn"            
1 1  9 "Husstandsoverhoveds fællesbarn"            
1 1  9 "Husstandsoverhoveds forældre"              
1 1  9 "Husstandsoverhoveds søskende"              
1 1  9 "Husstandsoverhoveds tjenestefolk"           
1 1 10 "Husstandsoverhoved"                         
1 1 10 "Husstandsoverhoveds ægtefælle"            
1 1 10 "Husstandsoverhoveds fællesbarn"            
1 1 10 "Husstandsoverhoveds fællesbarn"            
1 1 10 "Husstandsoverhoveds fællesbarn"            
1 1 10 "Husstandsoverhoveds fællesbarn"            
1 1 10 "Husstandsoverhoveds tjenestefolk"           
1 1 11 "Husstandsoverhoved"                         
1 1 11 "Husstandsoverhoveds ægtefælle"            
1 1 11 "Husstandsoverhoveds fællesbarn"            
1 1 11 "Husstandsoverhoveds tjenestefolk"           
1 1 11 "Husstandsoverhoveds tjenestefolk"           
1 1 11 "Husstandsoverhoveds familie"                
1 1 13 "Husstandsoverhoved"                         
1 1 13 "Husstandsoverhoveds ægtefælle"            
1 1 13 "Husstandsoverhoveds fællesbarn"            
1 1 13 "Husstandsoverhoveds fællesbarn"            
1 1 13 "Husstandsoverhoveds fællesbarn"            
1 1 14 "Husstandsoverhoved"                         
1 1 14 "Husstandsoverhoveds ægtefælle"            
1 1 14 "Husstandsoverhoveds fællesbarn"            
1 1 16 "Husstandsoverhoved"                         
1 1 16 "Husstandsoverhoveds ægtefælle"            
1 1 16 "Husstandsoverhoveds fællesbarn"            
1 1 16 "Husstandsoverhoveds logerende"              
1 1 16 "Husstandsoverhoveds logerende"              
1 1 16 "Husstandsoverhoveds familie"                
1 1 16 "Husstandsoverhoveds logerende"              
1 1 17 "Husstandsoverhoved"                         
1 1 17 "Husstandsoverhoveds ægtefælle"            
1 1 17 "Husstandsoverhoveds fællesbarn"            
1 1 17 "Husstandsoverhoveds tjenestefolk"           
1 1 19 "Husstandsoverhoved"                         
1 1 19 "Husstandsoverhoveds ægtefælle"            
1 1 19 "Husstandsoverhoveds fællesbarn"            
1 1 19 "Husstandsoverhoveds logerende"              
1 1 20 "Husstandsoverhoved"                         
1 1 20 "Husstandsoverhoveds ægtefælle"            
1 1 20 "Husstandsoverhoveds fællesbarn"            
1 1 21 "Husstandsoverhoved"                         
1 1 21 "Husstandsoverhoveds ægtefælle"            
1 1 21 "Husstandsoverhoveds fællesbarn"            
1 1 22 "Husstandsoverhoved"                         
1 1 22 "Husstandsoverhoveds ægtefælle"            
1 1 22 "Husstandsoverhoveds fællesbarn"            
1 1 22 "Husstandsoverhoveds fællesbarn"            
1 1 22 "Husstandsoverhoveds fællesbarn"            
1 1 22 "Husstandsoverhoveds fællesbarn"            
1 1 23 "Husstandsoverhoved"                         
1 1 23 "Husstandsoverhoveds ægtefælle"            
1 1 23 "Husstandsoverhoveds fællesbarn"            
1 1 24 "Husstandsoverhoved"                         
1 1 24 "Husstandsoverhoveds ægtefælle"            
1 1 24 "Husstandsoverhoveds fællesbarn"            
1 1 24 "Husstandsoverhoveds fællesbarn"            
1 1 24 "Husstandsoverhoveds fællesbarn"            
1 1 24 "Husstandsoverhoveds familie"                
1 1 24 "Husstandsoverhoveds tjenestefolk"           
1 1 24 "Husstandsoverhoveds tjenestefolk"           
1 1 24 "Husstandsoverhoveds tjenestefolk"           
1 1 24 "Husstandsoverhoveds tjenestefolk"           
1 1 25 "Husstandsoverhoved"                         
1 1 25 "Husstandsoverhoveds ægtefælle"            
1 1 25 "Husstandsoverhoveds fællesbarn"            
1 1 25 "Husstandsoverhoveds fællesbarn"            
1 1 25 "Husstandsoverhoveds plejebarn"              
1 1 25 "Husstandsoverhoveds tjenestefolk"           
1 1 25 "Husstandsoverhoveds tjenestefolk"           
1 1 27 "Husstandsoverhoved"                         
1 1 27 "Husstandsoverhoveds ægtefælle"            
1 1 27 "Husstandsoverhoveds fællesbarn"            
1 1 27 "Husstandsoverhoveds logerende"              
1 1 27 "Husstandsoverhoveds logerende"              
1 1 27 "Husstandsoverhoveds logerendes tjenestefolk"
1 1 27 "Husstandsoverhoveds tjenestefolk"           
1 1 27 "Husstandsoverhoveds tjenestefolk"           
1 1 27 "Husstandsoverhoveds tjenestefolk"           
1 1 30 "Husstandsoverhoved"                         
1 1 30 "Husstandsoverhoveds ægtefælle"            
end

Danish:
husstandsoverhoved: head of household.
tjenestefolk: servant
søskende: siblings.

so these are all the positions that comes up, when it should only be families with either "hustandsoverhoved, ægtefælle, or fællesbarn" (headofhouse, wife, child)

what do?

[Anna Frost]

Is there really no command that basically says: these values and ONLY these values get counted into a subgroup?

[Carlo Lazzaro]

Anna:
perhaps the following code will help:

g flag=1 if head_in_household==1 & spouse_in_household==1 & child_in_household==1
egen test=rowtotal( head_in_household child_in_household spouse_in_household ) if flag==1

[Anna Frost]

Hi Carlo, ran your code, and it didn't work. Still gives me families that also contains the other possible values. Sorry, but I appreciate everybody's help.

when i browse test it just gives "3" to every observation.

dataex nuclear_familytest kernefamilie husstnr test stilling_i_husstand_std if nuclear_familytest
> ==1

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(nuclear_familytest kernefamilie) long husstnr float test strL stilling_i_husstand_std
1 1  2 3 "Husstandsoverhoved"                         
1 1  2 3 "Husstandsoverhoveds ægtefælle"            
1 1  2 3 "Husstandsoverhoveds fællesbarn"            
1 1  2 3 "Husstandsoverhoveds logerende"              
1 1  3 3 "Husstandsoverhoved"                         
1 1  3 3 "Husstandsoverhoveds ægtefælle"            
1 1  3 3 "Husstandsoverhoveds fællesbarn"            
1 1  3 3 "Husstandsoverhoveds fællesbarn"            
1 1  3 3 "Husstandsoverhoveds fællesbarn"            
1 1  3 3 "Husstandsoverhoveds fællesbarn"            
1 1  3 3 "Husstandsoverhoveds ægtefælles forældre" 
1 1  3 3 "Husstandsoverhoveds tjenestefolk"           
1 1  9 3 "Husstandsoverhoved"                         
1 1  9 3 "Husstandsoverhoveds ægtefælle"            
1 1  9 3 "Husstandsoverhoveds fællesbarn"            
1 1  9 3 "Husstandsoverhoveds fællesbarn"            
1 1  9 3 "Husstandsoverhoveds fællesbarn"            
1 1  9 3 "Husstandsoverhoveds fællesbarn"            
1 1  9 3 "Husstandsoverhoveds forældre"              
1 1  9 3 "Husstandsoverhoveds søskende"              
1 1  9 3 "Husstandsoverhoveds tjenestefolk"           
1 1 10 3 "Husstandsoverhoved"                         
1 1 10 3 "Husstandsoverhoveds ægtefælle"            
1 1 10 3 "Husstandsoverhoveds fællesbarn"            
1 1 10 3 "Husstandsoverhoveds fællesbarn"            
1 1 10 3 "Husstandsoverhoveds fællesbarn"            
1 1 10 3 "Husstandsoverhoveds fællesbarn"            
1 1 10 3 "Husstandsoverhoveds tjenestefolk"           
1 1 11 3 "Husstandsoverhoved"                         
1 1 11 3 "Husstandsoverhoveds ægtefælle"            
1 1 11 3 "Husstandsoverhoveds fællesbarn"            
1 1 11 3 "Husstandsoverhoveds tjenestefolk"           
1 1 11 3 "Husstandsoverhoveds tjenestefolk"           
1 1 11 3 "Husstandsoverhoveds familie"                
1 1 13 3 "Husstandsoverhoved"                         
1 1 13 3 "Husstandsoverhoveds ægtefælle"            
1 1 13 3 "Husstandsoverhoveds fællesbarn"            
1 1 13 3 "Husstandsoverhoveds fællesbarn"            
1 1 13 3 "Husstandsoverhoveds fællesbarn"            
1 1 14 3 "Husstandsoverhoved"                         
1 1 14 3 "Husstandsoverhoveds ægtefælle"            
1 1 14 3 "Husstandsoverhoveds fællesbarn"            
1 1 16 3 "Husstandsoverhoved"                         
1 1 16 3 "Husstandsoverhoveds ægtefælle"            
1 1 16 3 "Husstandsoverhoveds fællesbarn"            
1 1 16 3 "Husstandsoverhoveds logerende"              
1 1 16 3 "Husstandsoverhoveds logerende"              
1 1 16 3 "Husstandsoverhoveds familie"                
1 1 16 3 "Husstandsoverhoveds logerende"              
1 1 17 3 "Husstandsoverhoved"                         
1 1 17 3 "Husstandsoverhoveds ægtefælle"            
1 1 17 3 "Husstandsoverhoveds fællesbarn"            
1 1 17 3 "Husstandsoverhoveds tjenestefolk"           
1 1 19 3 "Husstandsoverhoved"                         
1 1 19 3 "Husstandsoverhoveds ægtefælle"            
1 1 19 3 "Husstandsoverhoveds fællesbarn"            
1 1 19 3 "Husstandsoverhoveds logerende"              
1 1 20 3 "Husstandsoverhoved"                         
1 1 20 3 "Husstandsoverhoveds ægtefælle"            
1 1 20 3 "Husstandsoverhoveds fællesbarn"            
1 1 21 3 "Husstandsoverhoved"                         
1 1 21 3 "Husstandsoverhoveds ægtefælle"            
1 1 21 3 "Husstandsoverhoveds fællesbarn"            
1 1 22 3 "Husstandsoverhoved"                         
1 1 22 3 "Husstandsoverhoveds ægtefælle"            
1 1 22 3 "Husstandsoverhoveds fællesbarn"            
1 1 22 3 "Husstandsoverhoveds fællesbarn"            
1 1 22 3 "Husstandsoverhoveds fællesbarn"            
1 1 22 3 "Husstandsoverhoveds fællesbarn"            
1 1 23 3 "Husstandsoverhoved"                         
1 1 23 3 "Husstandsoverhoveds ægtefælle"            
1 1 23 3 "Husstandsoverhoveds fællesbarn"            
1 1 24 3 "Husstandsoverhoved"                         
1 1 24 3 "Husstandsoverhoveds ægtefælle"            
1 1 24 3 "Husstandsoverhoveds fællesbarn"            
1 1 24 3 "Husstandsoverhoveds fællesbarn"            
1 1 24 3 "Husstandsoverhoveds fællesbarn"            
1 1 24 3 "Husstandsoverhoveds familie"                
1 1 24 3 "Husstandsoverhoveds tjenestefolk"           
1 1 24 3 "Husstandsoverhoveds tjenestefolk"           
1 1 24 3 "Husstandsoverhoveds tjenestefolk"           
1 1 24 3 "Husstandsoverhoveds tjenestefolk"           
1 1 25 3 "Husstandsoverhoved"                         
1 1 25 3 "Husstandsoverhoveds ægtefælle"            
1 1 25 3 "Husstandsoverhoveds fællesbarn"            
1 1 25 3 "Husstandsoverhoveds fællesbarn"            
1 1 25 3 "Husstandsoverhoveds plejebarn"              
1 1 25 3 "Husstandsoverhoveds tjenestefolk"           
1 1 25 3 "Husstandsoverhoveds tjenestefolk"           
1 1 27 3 "Husstandsoverhoved"                         
1 1 27 3 "Husstandsoverhoveds ægtefælle"            
1 1 27 3 "Husstandsoverhoveds fællesbarn"            
1 1 27 3 "Husstandsoverhoveds logerende"              
1 1 27 3 "Husstandsoverhoveds logerende"              
1 1 27 3 "Husstandsoverhoveds logerendes tjenestefolk"
1 1 27 3 "Husstandsoverhoveds tjenestefolk"           
1 1 27 3 "Husstandsoverhoveds tjenestefolk"           
1 1 27 3 "Husstandsoverhoveds tjenestefolk"           
1 1 30 3 "Husstandsoverhoved"                         
1 1 30 3 "Husstandsoverhoveds ægtefælle"            
end

[Marcos Almeida]

when i browse test it just gives "3" to every observation.

But the data you shared only gives 3 to every observation.

[Weiwen Ng]

Hi Weiwen, I have not been introduced to cond before, but from what I can tell, this does not help me. The problem is of course that I have in my dataset households where the conditions ARE true, that being that there is a head of household, a wife, and their own kid(s) but some of the families also have one or more of the other values( such as an aunt living there) And what I want is to be sure that families that have more than just these three types of position_in_household does not get counted.

so I ran your code and it still counts families that consist of more than just these three conditions.
...
Danish:
husstandsoverhoved: head of household.
tjenestefolk: servant
søskende: siblings.

so these are all the positions that comes up, when it should only be families with either "hustandsoverhoved, ægtefælle, or fællesbarn" (headofhouse, wife, child)

what do?

Whoops! I forgot about that. I would generate a flag indicating if the person is not a head of household, child, or spouse.

gen other = spouse == 0 & child == 0 & head_of_house == 0
tab position_in_household_std if other == 1
              position_in_household_std |      Freq.     Percent        Cum.
----------------------------------------+-----------------------------------
            Husstandsoverhoveds familie |          2        5.71        5.71
           Husstandsoverhoveds forældre |          1        2.86        8.57
   Husstandsoverhoveds fællesbarns barn |          4       11.43       20.00
          Husstandsoverhoveds logerende |          9       25.71       45.71
          Husstandsoverhoveds plejebarn |          1        2.86       48.57
            Husstandsoverhoveds særbarn |          2        5.71       54.29
           Husstandsoverhoveds søskende |          3        8.57       62.86
       Husstandsoverhoveds tjenestefolk |         12       34.29       97.14
Husstandsoverhoveds ægtefælles forældre |          1        2.86      100.00
----------------------------------------+-----------------------------------
                                  Total |         35      100.00

If that looks correct, then just alter the -cond- statement a bit:

sort householdnumber
by householdnumber: egen other_in_hh = max(other)
by householdnumber: egen nuclear_family = max(cond(spouse_in_household == 1 & child_in_household == 1 & head_in_household == 1 & other_in_hh == 0, 1, 0))

Hopefully this works!

[Daniel Bela]

Another solution (not that efficient, but maybe easier to understand):

clear
input long(postid householdnumber) str150 position_in_household_std float(spouse spouse_in_household child child_in_household head_of_house head_in_household)
1 1 "Husstandsoverhoved" 0 1 0 0 1 1
2 1 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
3 1 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
4 2 "Husstandsoverhoved" 0 1 0 1 1 1
5 2 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
6 2 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
7 2 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
8 3 "Husstandsoverhoved" 0 1 0 1 1 1
9 3 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
10 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
11 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
12 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
13 3 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
14 3 "Husstandsoverhoveds ægtefælles forældre" 0 1 0 1 0 1
15 3 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
16 4 "Husstandsoverhoved" 0 1 0 0 1 1
17 4 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
18 4 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
19 4 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
20 5 "Husstandsoverhoved" 0 0 0 0 1 1
21 5 "Husstandsoverhoveds særbarn" 0 0 0 0 0 1
22 5 "Husstandsoverhoveds særbarn" 0 0 0 0 0 1
23 5 "Husstandsoverhoveds logerende" 0 0 0 0 0 1
24 5 "Husstandsoverhoveds logerende" 0 0 0 0 0 1
25 5 "Husstandsoverhoveds logerende" 0 0 0 0 0 1
26 6 "Husstandsoverhoved" 0 1 0 0 1 1
27 6 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
28 6 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
29 7 "Husstandsoverhoved" 0 0 0 1 1 1
30 7 "Husstandsoverhoveds søskende" 0 0 0 1 0 1
31 7 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
32 7 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
33 7 "Husstandsoverhoveds logerende" 0 0 0 1 0 1
34 8 "Husstandsoverhoved" 0 1 0 0 1 1
35 8 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
36 8 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
37 9 "Husstandsoverhoved" 0 1 0 1 1 1
38 9 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
39 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
40 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
41 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
42 9 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
43 9 "Husstandsoverhoveds forældre" 0 1 0 1 0 1
44 9 "Husstandsoverhoveds søskende" 0 1 0 1 0 1
45 9 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
46 10 "Husstandsoverhoved" 0 1 0 1 1 1
47 10 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
48 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
49 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
50 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
51 10 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
52 10 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
53 11 "Husstandsoverhoved" 0 1 0 1 1 1
54 11 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
55 11 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
56 11 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
57 11 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
58 11 "Husstandsoverhoveds familie" 0 1 0 1 0 1
59 12 "Husstandsoverhoved" 0 0 0 1 1 1
60 12 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
61 12 "Husstandsoverhoveds fællesbarn" 0 0 1 1 0 1
62 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
63 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
64 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
65 12 "Husstandsoverhoveds fællesbarns barn" 0 0 0 1 0 1
66 13 "Husstandsoverhoved" 0 1 0 1 1 1
67 13 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
68 13 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
69 13 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
70 13 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
71 14 "Husstandsoverhoved" 0 1 0 1 1 1
72 14 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
73 14 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
74 15 "Husstandsoverhoved" 0 1 0 0 1 1
75 15 "Husstandsoverhoveds ægtefælle" 1 1 0 0 0 1
76 15 "Husstandsoverhoveds tjenestefolk" 0 1 0 0 0 1
77 16 "Husstandsoverhoved" 0 1 0 1 1 1
78 16 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
79 16 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
80 16 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
81 16 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
82 16 "Husstandsoverhoveds familie" 0 1 0 1 0 1
83 16 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
84 17 "Husstandsoverhoved" 0 1 0 1 1 1
85 17 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
86 17 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
87 17 "Husstandsoverhoveds tjenestefolk" 0 1 0 1 0 1
88 18 "Husstandsoverhoved" 0 0 0 0 1 1
89 18 "Husstandsoverhoveds plejebarn" 0 0 0 0 0 1
90 18 "Husstandsoverhoveds søskende" 0 0 0 0 0 1
91 19 "Husstandsoverhoved" 0 1 0 1 1 1
92 19 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
93 19 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
94 19 "Husstandsoverhoveds logerende" 0 1 0 1 0 1
95 20 "Husstandsoverhoved" 0 1 0 1 1 1
96 20 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
97 20 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
98 21 "Husstandsoverhoved" 0 1 0 1 1 1
99 21 "Husstandsoverhoveds ægtefælle" 1 1 0 1 0 1
100 21 "Husstandsoverhoveds fællesbarn" 0 1 1 1 0 1
end
// translate to english -- this may be inadequate for some translations, sorry
replace position_in_household_std="head" if (position_in_household_std=="Husstandsoverhoved")
replace position_in_household_std="other family member" if (position_in_household_std=="Husstandsoverhoveds familie")
replace position_in_household_std="head's parent" if (position_in_household_std=="Husstandsoverhoveds forældre")
replace position_in_household_std="head's child" if (position_in_household_std=="Husstandsoverhoveds fællesbarn")
replace position_in_household_std="head's grandchild" if (position_in_household_std=="Husstandsoverhoveds fællesbarns barn")
replace position_in_household_std="lodger" if (position_in_household_std=="Husstandsoverhoveds logerende")
replace position_in_household_std="head's foster child" if (position_in_household_std=="Husstandsoverhoveds plejebarn")
replace position_in_household_std="head's child from previous marriage" if (position_in_household_std=="Husstandsoverhoveds særbarn")
replace position_in_household_std="head's sibling" if (position_in_household_std=="Husstandsoverhoveds søskende")
replace position_in_household_std="servant" if (position_in_household_std=="Husstandsoverhoveds tjenestefolk")
replace position_in_household_std="head's spouse" if (position_in_household_std=="Husstandsoverhoveds ægtefælle")
replace position_in_household_std="head's parent-in-law" if (position_in_household_std=="Husstandsoverhoveds ægtefælles forældre")
compress

// generate total of already created spouse-/child-/head-indicators variables
egen hh_focuspersons=rowtotal(spouse_in_household child_in_household head_in_household)

// generate number of persons in household
bysort householdnumber (postid) : generate hh_size=_N

// mark focus households (where both of the above equal)
generate nuclear_family_marker=(hh_size==hh_focuspersons & hh_size==3)

Regards
Bela