30 day windows using columns

[Vishal Sharma]

Hi , I dont think -rangestat-will work for this issue using the -datex- output provided below

I would like to generate a variable called row30sum that calculates the sum of the omeday*** in the 30 day window prior to the dayzero variable. for example, if dayzero=100, I would like to calculate the sum of omeday70 to omeday 100 to a variable called row30sum. Ive tried variations of this with no luck:

forval i = 1/`=_N' {
local var1=dayzero
local var=dayzero-30
egen rowsum30=rowtotal(omeday`var' - omeday`var1')
}

any help would be appreciated.

thanks
Vishal

* Example generated by -dataex-. To install: ssc install dataex
clear
input double patid int dayzero double(omeday1818 omeday1819 omeday1820 omeday1821 omeday1822 omeday1823 omeday1824 omeday1825 omeday1826 omeday1827 omeday1828)
229108620944 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
138624881243 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
262315513115 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0 2.4000000953674316
  2317301203 1828                  0                300                108                108                 92                 92                 92    77                 77                 77                 77
162029252680 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
157531890121 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
180224401888 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
154423301612 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
158512642442 1828                  0                  0                  0                  0                  0                  0                  0   4.5                4.5                4.5                  9
274731512254 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
114229492675 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 12305271203 1828                 40                 40                 40                 40                 40                 40                 40    40                 40                 40                 40
268204022566 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
107602420944 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
162417542181 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
258601960944 1828                 64                 64                 64                 64                 64                 64                 64    64                 64                 64                 64
243622750821 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
140607310496 1828                100                100                100                100                100                100                100   100                100                100                225
 58424251762 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
136400471888 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
137501042659 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
316616690944 1828                 30                 30                 30                 30                 30                 30                 30    30                 30                 30                 30
131621952181 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
129713712899 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
247822372455 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 71617791243 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 35031153115 1828                 90                 90                 90                 90                 90                 90                 90    90                 90                 90                180
 71217931883 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
108802510442 1828               2880               2880               2880               2880               2880               2880               2880  2880               2880               2880               5760
195715790763 1828                630                630                630                630                630                630                630   630                630                990                630
116901972566 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
233622941741 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
262301212181 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
136404142381 1828                120                120                120                120                120                120                120   120                120                120                120
236412031560 1828                288                288                288                288                288                288                288   288                600                600                856
116904130944 1828                180                180                180                180                180                  0                180   180                180                180                180
 49100131778 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
102726192666 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
142928131393 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
155807620350 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
125015551571 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
190909141741 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 29514231243 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
301515482675 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
166924580593 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 99324251612 1828                416                416                416                416                416                416                416   416                416                416                416
 74018801571 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
146116521203 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 31727621303 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 54411792381 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0 12.500000953674316
156319850442 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
229405452384 1828               1536                768                768                768                768                768                768  1728                768                768                768
281723842384 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
279519552680 1828                 96                 96                 96                 96                 96                 96                 96    96                  8                  8                 92
118521111560 1828              274.5              274.5              274.5              274.5              274.5              274.5              274.5 274.5              274.5              274.5              274.5
318918191605 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
213401612602 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 99211690944 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 14405632384 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 37405122623 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                540
238118672675 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
234213752535 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                 40
162411421572 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
160106001883 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 87103050017 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
111409441741 1828                 45                 45                 45                 45                 45                 45                 45    45                 45                 45                 45
129509660944 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
220525300251 1828                176                184                168                168                160                160                160   160                160                160                160
243606331855 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 95016932619 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
314326900882 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
211629601741 1828                 18                 18                 18                 18                  0                  0                  0     0                  0                  0                  0
 49205111203 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
154527741657 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
227216561657 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
247806110004 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
103425431888 1828                 45                 45                 45                 45                 45                 45                 45    45                 45                 45                 45
125306861303 1828                 72                 72                 72                 72                 72                144                 72    72                 72                 72                 72
 78307681657 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
244502340780 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
243300271663 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
109504150507 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
116223501063 1828                 64                 64                 64                 64                 64                 64                 64    64                 64                 64                 64
232421772126 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 85417542971 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
183928460004 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
306209461602 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
206828690593 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
260813610004 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
277404922311 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
150816362055 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
185526550944 1828                234                234                234                234                234                234                234   234                234                234                234
108124741203 1828                 92                 92                 92                 92                 92                 92                 92    92                 92                 92                 92
 58526301888 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  3
267318672675 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
166928962007 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 51417060479 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
 48814632394 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
177905012680 1828                  0                  0                  0                  0                  0                  0                  0     0                  0                  0                  0
235601952384 1828 244.28571319580078 244.28571319580078 244.28571319580078 244.28571319580078 244.28571319580078 244.28571319580078 244.28571319580078   180 244.28571319580078 244.28571319580078 184.28571319580078
end

[Clyde Schechter]

Well, you don't actually need -rangestat- to do this. It can be done with just native Stata commands. But, as with most things in Stata, what is nearly impossible in wide layout is very easy in long. So it's just:

reshape long omeday, i(patid) j(day)
by patid, sort: egen sum30 = total(cond(inrange(dayzero-day, 0, 30), omeday, .))

If there is some compelling reason to return to the wide layout, you can follow that up with -reshape long-. But most likely the wide layout will just get in the way of everything else you plan to do with this data, so you may as well just leave it long unless there is a truly compelling reason not to.

[Vishal Sharma]

sounds good! I kept it wide since the data set is HUGE. I ll perform this on smaller sets using for loops.

Thanks again Clyde!

[William Lisowski]

I notice that you were earlier given advice about efficiently reshaping large datasets to which you did not respond.

https://www.statalist.org/forums/for...omputing-speed
https://www.statalist.org/forums/for...eshape-command

In the future if do not intend to follow recommendations to use reshape, it would be helpful if your initial post acknowledged that you feel that reshape will not work for you, to save those whose advice you seek the time spent writing up advice you feel you cannot follow. But based on the previous advice, it seems to me if you took the time to reshape your data to long and saved it in that layout, you will ultimately save time.

I think your code from post #1

forval i = 1/`=_N' {
local var1=dayzero
local var=dayzero-30
egen rowsum30=rowtotal(omeday`var' - omeday`var1')
}

might work if it looked more like this

generate rowsum30 = .
forval i = 1/`=_N' {
local var1=dayzero
local var=dayzero-30
egen temp=rowtotal(omeday`var' - omeday`var1') in `i'
replace rowsum30 = temp in `i'
drop temp
}

This is going to take a while to run, you might want to start with

forval i = 1/1000 {

so you can estimate from that how long it will take to process the whole dataset.

[Vishal Sharma]

Thanks William! I ll give that a try as well.

[Vishal Sharma]

Hi William , I tried the recommendation you made , however it does not update the local var1 and local var for each observation. So the values for the local macros stay fixed at the first observation. Any ways to have them updated to reflect the dayzero in each subsequent line?

Thanks
Vishal