Splitting observations and calculate the sum of previous years

[Emad Noureldeen]

Dears,

I have this dataset. First, I want to split the observations to reflect only one person. For example, I need this "2001 "000001" 120126983351 "Hou Lixun, Chen Airong"
" to be as follows and so on. and putted into a new variable say Names

2001 "000001" 120126983351 "Hou Lixun"
2001 "000001" 120126983351 "Chen Airong"

Secondly, I want to calculate the sum of Total_Assets for each person of the previous 3 years and create Sum_Assets. For example, if we are in 2010. The value of Sum_Assets for each person X in Names will equal the sum of assets for persons in Year 2009, 2008, and 2007.

Thanks in advance.

* Example generated by -dataex-. For more info, type help dataex
clear
input float Year str10 Stkcd double Total_Assets str50 SignatureCPA_en
2001 "000001"     120126983351 "Hou Lixun, Chen Airong"       
2002 "000001"     166166379400 "Hou Lixun, Chen Airong"       
2003 "000001"     192851003723 "Hou Lixun, Chen Airong"       
2004 "000001"     204286424020 "Yang Kejing, Chen Airong"     
2005 "000001"     229216415808 "Long Ying, Yang Kejing"       
2006 "000001"     260576263238 "Long Ying, Hou Lixun"         
2011 "000001"    1258176944000 "Zhang Xiaodong, Chang Hua"    
2012 "000001"    1606536760000 "Chang Hua, Zhou Daojun"       
2013 "000001"     1.891741e+12 "Yao Wenping, Zhu Liping"      
2014 "000001"     2.186459e+12 "Yao Wenping, Zhu Liping"      
2015 "000001"     2.507149e+12 "Yao Wenping, Zhu Liping"      
2016 "000001"     2.953434e+12 "Yao Wenping, Gan Lili"        
2017 "000001"     3.248474e+12 "Yao Wenping, Gan Lili"        
2018 "000001"     3.418592e+12 "Chen Anqiang, Gan Lili"       
2019 "000001"      3.93907e+12 "Chen Anqiang, Gan Lili"       
2020 "000001"     4.468514e+12 "Chen Anqiang, Liu Yufeng"     
2021 "000001"      4.92138e+12 "Chang Hua, Wang Yangyan"      
2022 "000001"     5.321514e+12 "Chang Hua, Wang Yangyan"      
2009 "000002"  137608554829.39 "Li Jiaqi, Li Wanwei"          
2011 "000002"  296208440030.05 "Li Wanwei, Wen Huaxin"        
2012 "000002"  378801615075.37 "Li Wanwei, Wen Huaxin"        
2013 "000002"  479205323490.54 "Li Wanwei, Wen Huaxin"        
2014 "000002"  508408755415.65 "Wang Xiaomei, Luo Yonghui"    
2015 "000002"  611295567689.29 "Wang Xiaomei, Luo Yonghui"    
2016 "000002"  830674213924.14 "Fang Gui, Chen Yongyi"        
2017 "000002" 1165346917804.55 "Fang Gui, Chen Yongyi"        
2018 "000002" 1528579356474.81 "Chen Yongyi, Fang Jiong"      
2019 "000002" 1729929450401.23 "Chen Yongyi, Li Moran"        
2020 "000002" 1869177094005.55 "Chen Yongyi, Li Moran"        
2021 "000002" 1938638128699.08 "Zhou Yongming, Li Moran"      
2022 "000002" 1757124444202.95 "Zhou Yongming, Li Moran"      
2001 "000004"     222008804.79 "Zhang Keli, Liang Feng"       
2002 "000004"     263614606.25 "Liang Feng, Yang Linying"     
2003 "000004"     237875156.04 "Liang Feng, Liu Renzhi"       
2004 "000004"     214270248.77 "Liu Renzhi, Liang Feng"       
2005 "000004"     155868770.91 "Liang Feng, Wen Aifeng"       
2006 "000004"     204304637.72 "Li Ping, Yang Shengqiao"      
2007 "000004"     169822593.95 "Wang Xiangfei, Shan Bo"       
2008 "000004"     167810585.24 "Shan Bo, Li Liying"           
2009 "000004"     230512058.69 "Wang Xiangfei, Sun Meilin"    
2010 "000004"      184219435.7 "Liu Han, Sun Meilin"          
2011 "000004"     196307219.48 "Liu Han, Zhao Jun"            
2012 "000004"     192913568.86 "Liu Han, Zhao Jun"            
2013 "000004"     242152079.61 "Xu De, Yan Guoxiang"          
2014 "000004"     338282581.12 "Wang Jianhua, Liu Guojun"     
2015 "000004"     398673507.14 "Yang Hongfei, Zhao Jun"       
2016 "000004"     223716293.01 "Yang Hongfei, Zhao Jun"       
2017 "000004"     268844295.64 "Hu Jinke, Zhao Jun"           
2018 "000004"     351177470.17 "Liu Jinping, Zhao Jun"        
2019 "000004"    1494490266.24 "Shen Hongbo, Zhao Jun"        
2020 "000004"    1563562870.86 "Shen Hongbo, Wan Haiqing"     
2021 "000004"    1110569957.85 "Fu Xijun, Zhuang Cuiman"      
2022 "000004"     575390741.06 "Hou Shengli, Du Li"           
2001 "000005"     1924315014.2 "Hu Chunyuan, Li Bingxin"      
2015 "000005"    2194008790.39 "Luo Yuelong, Zeng Yikai"      
2016 "000005"     2527235374.1 "Luo Yuelong, Zeng Yikai"      
2017 "000005"    2912099984.28 "Luo Yuelong, Yang Xu"         
2018 "000005"    3121423378.81 "Luo Yuelong, Yang Xu"         
2019 "000005"    3031840976.34 "Hu Bing, Zeng Yikai"          
2020 "000005"    2452795303.51 "Hu Bing, Zeng Yikai"          
2021 "000005"    2474377401.28 "Liu Fengmei, Gao Fengxia"     
2022 "000005"     2602189919.9 "Wang Dong, Wang Wei"          
2001 "000006"    4218937390.09 "Yin Jianmin, Bao Kai"         
2002 "000006"    4490880117.33 "Bao Kai, Yin Jianming"        
2003 "000006"    3968241655.76 "Yin Jianmin, Tian Jingliang"  
2004 "000006"    4020543824.74 "Yin Jianmin, Tian Jingliang"  
2005 "000006"    2553071988.37 "Yin Jianmin, Tian Jingliang"  
2006 "000006"    2918925351.04 "Yuan Longping, Tian Jingliang"
2007 "000006"    5568431161.83 "Tian Jingliang, Yuan Longping"
2008 "000006"    5921442329.83 "Yin Jianmin, Li Xihui"        
2011 "000006"     8321974063.4 "Li Xihui, Wu Yaya"            
2012 "000006"    9343430259.57 "Tian Jingliang, Zheng Lihong" 
2013 "000006"    9968160024.99 "Tian Jingliang, Wu Yaya"      
2014 "000006"   11753217519.19 "Tian Jingliang, Wu Yaya"      
2001 "000007"    1102173563.97 "Liang Feng, Li Guangdao"      
2002 "000007"    1150916322.07 "Liang Feng, Li Guangdao"      
2003 "000007"     1117684181.1 "Liang Feng, Li Guangdao"      
2004 "000007"    1048420272.23 "Li Guangdao, Liang Feng"      
2005 "000007"     854288325.12 "Liang Feng, Li Guangdao"      
2006 "000007"     805877586.43 "Wang Pei, Wen Aifeng"         
2007 "000007"      737182927.6 "Wang Pei, Wen Aifeng"         
2008 "000007"     610650389.64 "Wang Pei, Wen Aifeng"         
2009 "000007"     363271589.75 "Wang Pei, Wen Aifeng"         
2010 "000007"     332421568.09 "Wang Pei, Yi Yongjian"        
2011 "000007"     362415187.93 "Yi Yongjian, Zan Litao"       
2012 "000007"      789680006.5 "Li Zehao, Yi Yongjian"        
2013 "000007"     657511920.77 "Li Zehao, Yi Yongjian"        
2014 "000007"     680925246.96 "Li Zehao, Yi Yongjian"        
2015 "000007"     480364514.33 "Tang Wenbin, Xiao Lei"        
2016 "000007"     514367695.08 "Lu Jian, Yuan Wen"            
2017 "000007"     442675592.96 "Gong Qichun, Zhou Xiaochun"   
2018 "000007"     544709995.47 "Hu Fenfang, Xiao Zhijun"      
2019 "000007"     533693420.89 "Hu Fenfang, Xiao Zhijun"      
2020 "000007"     364394022.55 "Hu Fenfang, Xiao Zhijun"      
2021 "000007"     432015468.58 "Yu Longbin, Deng Xuelei"      
2022 "000007"     354185895.12 "Xiao Zhijun, Li Guoqiang"     
2001 "000008"     107596720.14 "Cui Yan, Wang Cuixian"        
2002 "000008"      70616135.84 "Wang Cuixian, Cui Yan"        
2003 "000008"       60455170.6 "Cui Yan, Wang Cuixian"        
2004 "000008"      90797725.73 "Wang Cuixian, Zhong Ping"     
end

[Nick Cox]

rangestat is from SSC.

* Example generated by -dataex-. For more info, type help dataex
clear
input float Year str10 Stkcd double Total_Assets str50 SignatureCPA_en
2001 "000001"     120126983351 "Hou Lixun, Chen Airong"       
2002 "000001"     166166379400 "Hou Lixun, Chen Airong"       
2003 "000001"     192851003723 "Hou Lixun, Chen Airong"       
2004 "000001"     204286424020 "Yang Kejing, Chen Airong"     
2005 "000001"     229216415808 "Long Ying, Yang Kejing"       
2006 "000001"     260576263238 "Long Ying, Hou Lixun"    
end 

gen long id = _n 
split SignatureCPA_en, parse(,) gen(Names)
reshape long Names, i(id) j(which)
replace Names = trim(Names)

rangestat (sum) TOTAL=Total, int(Year -2 0) by(Names)
format Total TOTAL %14.0f

sort Names Year 

list Names Year Total TOTAL 

     +--------------------------------------------------+
     |       Names   Year   Total_Assets          TOTAL |
     |--------------------------------------------------|
  1. | Chen Airong   2001   120126983351   120126983351 |
  2. | Chen Airong   2002   166166379400   286293362751 |
  3. | Chen Airong   2003   192851003723   479144366474 |
  4. | Chen Airong   2004   204286424020   563303807143 |
  5. |   Hou Lixun   2001   120126983351   120126983351 |
     |--------------------------------------------------|
  6. |   Hou Lixun   2002   166166379400   286293362751 |
  7. |   Hou Lixun   2003   192851003723   479144366474 |
  8. |   Hou Lixun   2006   260576263238   260576263238 |
  9. |   Long Ying   2005   229216415808   229216415808 |
 10. |   Long Ying   2006   260576263238   489792679046 |
     |--------------------------------------------------|
 11. | Yang Kejing   2004   204286424020   204286424020 |
 12. | Yang Kejing   2005   229216415808   433502839828 |
     +--------------------------------------------------+

[Emad Noureldeen]

rangestat is from SSC.

* Example generated by -dataex-. For more info, type help dataex
clear
input float Year str10 Stkcd double Total_Assets str50 SignatureCPA_en
2001 "000001" 120126983351 "Hou Lixun, Chen Airong"
2002 "000001" 166166379400 "Hou Lixun, Chen Airong"
2003 "000001" 192851003723 "Hou Lixun, Chen Airong"
2004 "000001" 204286424020 "Yang Kejing, Chen Airong"
2005 "000001" 229216415808 "Long Ying, Yang Kejing"
2006 "000001" 260576263238 "Long Ying, Hou Lixun"
end

gen long id = _n
split SignatureCPA_en, parse(,) gen(Names)
reshape long Names, i(id) j(which)
replace Names = trim(Names)

rangestat (sum) TOTAL=Total, int(Year -2 0) by(Names)
format Total TOTAL %14.0f

sort Names Year

list Names Year Total TOTAL

+--------------------------------------------------+
| Names Year Total_Assets TOTAL |
|--------------------------------------------------|
1. | Chen Airong 2001 120126983351 120126983351 |
2. | Chen Airong 2002 166166379400 286293362751 |
3. | Chen Airong 2003 192851003723 479144366474 |
4. | Chen Airong 2004 204286424020 563303807143 |
5. | Hou Lixun 2001 120126983351 120126983351 |
|--------------------------------------------------|
6. | Hou Lixun 2002 166166379400 286293362751 |
7. | Hou Lixun 2003 192851003723 479144366474 |
8. | Hou Lixun 2006 260576263238 260576263238 |
9. | Long Ying 2005 229216415808 229216415808 |
10. | Long Ying 2006 260576263238 489792679046 |
|--------------------------------------------------|
11. | Yang Kejing 2004 204286424020 204286424020 |
12. | Yang Kejing 2005 229216415808 433502839828 |
+--------------------------------------------------+

Thanks so much. It works, but there are some more observations that does not include names but include total numbers, and I do not know what is this total, should I drop?

[Nick Cox]

Your project, your choices.