Grouping data and deleting excess obervations

[Siebe Zwijsen]

Hey Stata friends,

I got an issue with my dataset that I cannot solve my own. My dataset looks as follows:

ID-CompanyName-Year-Month-Var1-Var-2-Var-3-Var4
1-Apple-2020-1-.-.-.-1
1-Apple-2020-1-.-.1-.
1-Apple-2020-1-.-1-.-.
1-Apple-2020-1-1-.-.-.
2-Banana-2020-1-.-.1-.
2-Banana-2020-1-.-.1-.
2-Banana-2020-1-.-1-.-.
2-Banana-2020-1-1-.-.-.
3-Citrus-2020-1-.-.1-.
3-Citrus-2020-1-.-.1-.
3-Citrus-2020-1-.-1-.-.
3-Citrus-2020-1-1-.-.-.

What I want in the end is 3 observations with all the data in it. so I want to group the data so that I got one observation with the 4 vars filled. Can anyone be my savior?

Siebe

[Nick Cox]

Please follow FAQ Advice #12 and use dataex to give a data example. Note that Var-3 and Var-4 could not be legal variable names, because - is not allowed in variable names.

Otherwise this may help:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte ID str6 CompanyName int Year byte(Month Var1) double Var2 byte(Var3 Var4)
1 "Apple"  2020 1 .  . . 1
1 "Apple"  2020 1 . .1 . .
1 "Apple"  2020 1 .  1 . .
1 "Apple"  2020 1 1  . . .
2 "Banana" 2020 1 . .1 . .
2 "Banana" 2020 1 . .1 . .
2 "Banana" 2020 1 .  1 . .
2 "Banana" 2020 1 1  . . .
3 "Citrus" 2020 1 . .1 . .
3 "Citrus" 2020 1 . .1 . .
3 "Citrus" 2020 1 .  1 . .
3 "Citrus" 2020 1 1  . . .
end

collapse (firstnm) Var*, by(ID CompanyName Year Month)

list 

     +----------------------------------------------------------+
     | ID   Compan~e   Year   Month   Var1   Var2   Var3   Var4 |
     |----------------------------------------------------------|
  1. |  1      Apple   2020       1      1     .1      .      1 |
  2. |  2     Banana   2020       1      1     .1      .      . |
  3. |  3     Citrus   2020       1      1     .1      .      . |
     +----------------------------------------------------------+

.