Reordering data entries

[Jad Tamimi]

Hello,

I have data which looks like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input str9(c711b c712b c713b)
"" "20231192" "20231243"
"" "20290920" ""        
"" "16210140" ""        
"" "25110110" ""        
"" "23910119" ""        
"" "16101101" ""        
"" ""         "23920212"
end

Each row (observation) is a firm, and these variables represent the top 3 most important products (identified by a 8-digit code) for that firm, for example c711b is the most important product for the firm and c712b is the second most important product. In this example I restricted my data to include cases where there is a missing entry for the top product but no missing entry for second or third product, which doesnt make sense. So what I would like to do is reorder these.

So for example the last observation in the data only reports the third most important product but no prior ones so, instead I would like this entry to be in the first slot (so variable c711b instead of c713b). Similarly for the first observation I would like to shift the entries one column to the left. I would like to do this for all cases, but I also have other variables in the data and I dont wanna mess up the ordering of those variables, just these three, please let me know how I can implement this.

Thanks,
Jad

[Nick Cox]

* Example generated by -dataex-. For more info, type help dataex
clear
input str9(c711b c712b c713b)
"" "20231192" "20231243"
"" "20290920" ""        
"" "16210140" ""        
"" "25110110" ""        
"" "23910119" ""        
"" "16101101" ""        
"" ""         "23920212"
end 

gen long id = _n 

reshape long c@b, i(id) j(which)

bysort id (which) : drop if missing(cb)  

l, sepby(id)

by id : replace which = _n 

reshape wide cb, i(id) j(which)

l 


     +--------------------------+
     | id        cb1        cb2 |
     |--------------------------|
  1. |  1   20231192   20231243 |
  2. |  2   20290920            |
  3. |  3   16210140            |
  4. |  4   25110110            |
  5. |  5   23910119            |
     |--------------------------|
  6. |  6   16101101            |
  7. |  7   23920212            |
     +--------------------------+

[Jad Tamimi]

Thanks Nick, this did the trick. I need to become more confident with reshaping my data as I always find myself needing help with this one, I studied your code well here and I definitely feel a bit more confident.

[Nick Cox]

There are lots of jokes about reshape on social media, some of them funny. It doesn't deserve its often negative reputation. The essence is that Stata can't read your mind on what you want -- you need to spell it out.

A while back I gathered a rag-bag of extra tips into https://www.stata.com/support/faqs/d...-with-reshape/

I'd write about it in the Stata Journal if I thought I could improve on the documentation, but that's always a strong challenge. Like some other topics -- merge is one, dates is another -- different details bite in different cases, and covering even the most common would just make for a lengthy account that wouldn't be much fun for anyone.

[Jad Tamimi]

Hello Nick, Unfortunately I realized some of my observations were dropped because of the code, I made a slight adaptation so that observations would not be dropped:

gen long id = _n 
reshape long c71@b, i(id) j(order)
replace order=. if missing(c71b)
gsort id order
by id : replace order = _n 
reshape wide c71b, i(id) j(order)

Thanks!