Creating a new variable using foreach

[Sarah Haynes]

Hello,

I have a dataset that includes 180 variables (day1, day2, day3...day180) that can take on the values 0,1,2,3, or 7. I have another variable (intlag) which can take on values from 1 to 180. I am trying to create a variable in this wide dataset that will represent the number of the day if the value for that day is 7 and if the number of the day is equal to the intlag variable (for example, the value of newvar=18 if day18=7 and intlag=18).

I was thinking I could accomplish this with a foreach loop but haven't had any luck when looking for examples.

I have included a dataex example below.

Any help would be much appreciated!

Thank you,

Sarah

* Example generated by -dataex-. To install: ssc install dataex
clear
input long pid float intlag byte(day1 day2 day3 day4 day5 day6 day7 day8 day9 day10 day11 day12 day13 day14 day15 day16 day17 day18 day19 day20)
1000008 112 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 0
1000008 112 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 0
1000008 128 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 0
1000008   . 0 0 0 0 0 0 0 0 0 0 0 1 0 1 0 1 0 0 1 0
1000048  10 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048  10 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048  20 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048  39 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048  80 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048  80 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048  83 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000048   . 0 0 0 0 1 1 1 2 2 2 0 2 2 2 2 2 2 2 2 7
1000063  18 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 0 1
1000063 127 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 0 1
1000063 145 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 0 1
1000063   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 2 2 2 2 0 1
1000081  68 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0
1000081  87 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0
1000081 113 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0
1000081   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 0 0 1 0
1000089  30 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0
1000089 163 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0
1000089   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 0 0
1000098 175 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1
1000098   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1
1000111  92 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000111   . 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1
1000115 136 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000115   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000130  32 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000130 126 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000130 144 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000130   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000157  68 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157  82 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157 103 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157 103 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157 118 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157 128 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157 150 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000157   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 0 1
1000160  21 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000160  97 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000160 118 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000160   . 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1
1000180  70 0 0 0 0 0 0 1 1 1 1 1 0 0 1 0 0 1 0 1 1
1000180   . 0 0 0 0 0 0 1 1 1 1 1 0 0 1 0 0 1 0 1 1
1000213  16 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000213  33 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000213  53 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000213 121 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000213 136 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000213 156 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000213   . 0 0 0 0 0 0 0 0 0 2 2 2 2 2 2 7 0 0 0 0
1000271   3 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271   9 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  24 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  24 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  31 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  43 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  58 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  58 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271  83 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000271   . 0 0 6 0 0 0 0 0 0 0 1 0 0 0 0 0 0 0 1 0
1000296  54 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1
1000296   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1
1000312 145 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000312 158 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000312   . 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
1000319  17 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319 105 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319 125 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319 125 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319 138 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319 158 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319 173 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000319   . 0 0 0 1 1 1 1 1 0 1 1 1 1 1 1 1 4 0 0 0
1000334  43 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334  43 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334  55 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334  71 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334  87 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334 113 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334 136 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334 158 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334 173 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000334   . 0 0 0 1 0 0 0 0 0 0 1 1 0 0 0 0 0 0 0 1
1000346  31 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 1 1 1
1000346   . 0 0 0 0 0 0 0 0 0 0 0 1 1 0 1 1 1 1 1 1
1000354  37 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 7 2 2
1000354  49 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 7 2 2
1000354  73 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 7 2 2
1000354   . 0 0 0 0 0 0 0 0 2 2 2 2 2 2 2 2 2 7 2 2
1000385  20 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000385 104 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000385 124 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000385 124 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000385 131 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000385   . 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 2 2
1000390 163 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1
end

[Justin Niakamal]

I'm not sure if I'm interpreting your request correctly (I apologize if not), but here's my interpretation

gen flag = . 
forvalues x = 1/180 {
    qui replace flag = `x' if day`x' == 7 & intlag == `x'
}

[Clyde Schechter]

Here's another approach. Justin Blasongame's is simpler in a way. My approach involves creating a long data set, which then eliminates the need for a loop. I provided a -reshape wide- near the end to get you back to the layout you started with, but if you are planning further analysis involving the day* variables, you probably should dispense with that command and leave the data in long layout, as the majority of data management and analysis commands in Stata work better (or only) with long data.

gen long obs_no = _n
reshape long day, i(obs_no) j(day_num)

by obs_no (day_num), sort: gen new_var = day[intlag] == 7
reshape wide
sort obs_no
order pid new_var intlag, first

By the way, I had to create the variable obs_no in order for this approach to work because you do not have any variables in the data that uniquely identify observations. In fact, a substantial number of observations are complete duplicates of other observations (agreeing on all variables). There is no law against that, but it is somewhat unusual for data sets to intentionally include completely duplicate observations: it is usually a sign that something went wrong with the data management that created the data set. While the duplicates themselves are harmless, and easily disposed of with -duplicates drop-, data errors are a bit like cockroaches: you seldom have just one. So unless you have a clear understanding why there are, and should be, duplicate observations in your data, I urge you to revisit the creation of this data set (or ask whoever created it to do so)--there is a decent chance you will find other errors as well.