How to combine two observations with identical date value and different variable to one row containing information from both variables?

[Kasper Kotisaari]

Hello everyone!

I am relatively new to Stata but have some previous experience with R.
I am stuck with a relatively simple problem: my data is sorted by date value, and each date value occurs two times in the data set.
Besides date value, the data contains two variables, let's call them 'minimum' and 'maximum'.
Every other row has the value for minimum while maximum being missing, and every other row has the value for maximum while minimum being missing.
How can I combine these two rows with same date value to one row containing values for date, minimum and maximum?

The data looks like this:

date - - - - - - - - - minimum - - - - - - - - - maximum
19980101 - - - - - -5 - - - - -- - - - - - - - - - - - - .
19980101 - - - - - - . - - - - - - - - - - - - - - - - - 17
19980102 - - - - - -8 - - - - - - - - - - - - - - - - - - .
19980102 - - - - - - . - - - - - - - - - - - - - - - - - 14
19980103 - - - - - -3 - - - - - - - - - - - - - - - - - .
19980103 - - - - - - . - - - - - - - - - - - - - - - - - 15
...
...
...


If I were to do this with R I would simply sort by date and then loop over all rows and append every odd index value from column1 and column2 to their own lists, and every even indexed value from column3 to another list, then combine the lists according to index value.

Am I missing something simple here, or am I perhaps not knowing how to look at this problem through the-Stata-way-of-doing-things glasses?

Thank you already in advance!

[Nick Cox]

On this information, collapse will solve your problem. Note my use of dataex as requested in the prompt to posters and the forum FAQ.

* Example generated by -dataex-. For more info, type help dataex
clear
input long date byte(minimum maximum)
19980101 5  .
19980101 . 17
19980102 8  .
19980102 . 14
19980103 3  .
19980103 . 15
end

collapse min max, by(date)

list 

     +------------------------------+
     |     date   minimum   maximum |
     |------------------------------|
  1. | 19980101         5        17 |
  2. | 19980102         8        14 |
  3. | 19980103         3        15 |
     +------------------------------+

If your date variable is numeric with integer values like 19980101 or string with values like "19980101", it will sooner or later need a fix.

To see that, note that Stata will not treat the jump from 19981231 to 19990101 as you wish.

See https://journals.sagepub.com/doi/pdf...6867X251341416 for one overview of handling dates and times.

[Kasper Kotisaari]

Thanks!
I also just finally figured out a solution to this question, I'll post it here too just in case:

/* generate an index i for minimums and maximums for sorting */

gen i = 1
replace i = 2 if minimum == .

/* generate new column for the values to be taken from odd rows */

gen newcolumn = 0

/* sort the data by date and the index to be sure the rows are in order thoughout the data */

sort date i

/* The main thing I found somewhere:
For all even rows (indicated now by the index 2), which already contain maximum value, let's take the minimum value from the previous row (indicated by "_n-1") */

replace newcolumn = minimum[_n-1] if i==2

/* then just drop the minimum column and columns where maximum is empty, (+rename the newcolumn if needed) */

drop minimum
drop if maximum==.
*rename newcolumn minimum

[Kasper Kotisaari]

On this information, collapse will solve your problem. Note my use of dataex as requested in the prompt to posters and the forum FAQ.

* Example generated by -dataex-. For more info, type help dataex
clear
input long date byte(minimum maximum)
19980101 5 .
19980101 . 17
19980102 8 .
19980102 . 14
19980103 3 .
19980103 . 15
end

collapse min max, by(date)

list

+------------------------------+
| date minimum maximum |
|------------------------------|
1. | 19980101 5 17 |
2. | 19980102 8 14 |
3. | 19980103 3 15 |
+------------------------------+

If your date variable is numeric with integer values like 19980101 or string with values like "19980101", it will sooner or later need a fix.

To see that, note that Stata will not treat the jump from 19981231 to 19990101 as you wish.

See https://journals.sagepub.com/doi/pdf...6867X251341416 for one overview of handling dates and times.

This solution looks very efficient! However it seems to drop possible other variables from the data in memory. Thank you for your note on peculiarities with the date format in Stata.

[Nick Cox]

Well, in #1 you said

Besides date value, the data contains two variables, let's call them 'minimum' and 'maximum'.

which doesn't explicitly rule out yet more variables, but it does say nothing about them. What's key therefore is what you want to do with data for any other variables.

[Hemanshu Kumar]

If you have other variables and you don't want to lose any rows (observations) of your data because of information in those other ones, here is a quick alternative to #2, using the same data example:

clear
input long date byte(minimum maximum)
19980101 5  .
19980101 . 17
19980102 8  .
19980102 . 14
19980103 3  .
19980103 . 15
end

rename (minimum maximum) _=
egen minimum = max(_minimum), by(date)
egen maximum = max(_maximum), by(date)

drop _minimum _maximum

which produces:

. list , noobs sep(0)

  +------------------------------+
  |     date   minimum   maximum |
  |------------------------------|
  | 19980101         5        17 |
  | 19980101         5        17 |
  | 19980102         8        14 |
  | 19980102         8        14 |
  | 19980103         3        15 |
  | 19980103         3        15 |
  +------------------------------+

[Nick Cox]

Hemanshu Kumar 's nice solution can be simplified as follows:

bysort date (maximum) : replace maximum = maximum[1]  
bysort date (minimum) : replace minimum = minimum[1]

[Kasper Kotisaari]

Hemanshu Kumar 's nice solution can be simplified as follows:

bysort date (maximum) : replace maximum = maximum[1]
bysort date (minimum) : replace minimum = minimum[1]

Thank you Nick Cox and Hemanshu Kumar , this solution is perfect!