change to long format

[Sartaj Hussain]

I want to change the following dataset to long format. The data example is given below: There are three variables: pbratio, pr and mcap. Date is in the first row. The long format should follow the order:

stock_id stock date pr pbratio mcap

* Example generated by -dataex-. For more info, type help dataex
clear
input str54 stock str10(pbratio pr mcap)
""                                        "01-03-2000" "01-08-2000" "02-08-2000"
"20 Microns Ltd."                         ""           ""           ""          
"3I Infotech Ltd."                        ""           ""           ""          
"3M India Ltd."                           "10.96"      "714.25"     "677.35"    
"3P Land Holdings Ltd."                   "0.12"       ""           ""          
"52 Weeks Entertainment Ltd."             ""           ""           ""          
"5Paisa Capital Ltd."                     ""           ""           ""          
"63 Moons Technologies Ltd."              "28.89"      "104.25"     "120.9"     
"7Nr Retail Ltd."                         ""           ""           ""          
"7Seas Entertainment Ltd."                ""           ""           ""          
"A & M Febcon Ltd."                       ""           ""           ""          
"A 2 Z Infra Engg. Ltd."                  ""           ""           ""          
"A A Plus Tradelink Ltd."                 ""           ""           ""          
"A A R Commercial Co. Ltd. [Merged]"      ""           ""           ""          
"A A R V Infratel Ltd."                   ""           ""           ""          
"A B B India Ltd."                        "2.46"       "40.73"      "40.59"     
"A B C Gas (International) Ltd."          "4.32"       "2"          "2"         
"A B C India Ltd."                        "0.11"       ""           ""          
"A B M Knowledgeware Ltd."                "2.09"       "4.13"       "4.25"      
"A C C Ltd."                              "2.12"       "113.05"     "116.7"     
"A C E Edutrend Ltd."                     ""           ""           ""          
"A C I Infocom Ltd."                      "2.33"       ""           ""          
"A D C India Communications Ltd."         "7.24"       "456.95"     "441.1"     
"A D F Foods Ltd."                        "0.28"       "7.05"       "7.7"       
"A D S Diagnostic Ltd."                   ""           ""           ""          
"A F Enterprises Ltd."                    ""           ""           ""          
"A G I Greenpac Ltd."                     "0.89"       "25.8"       ""          
"A G I Infra Ltd."                        ""           ""           ""          
"A G S Transact Technologies Ltd."        ""           ""           ""          
"A I A Engineering Ltd."                  ""           ""           ""          
"A I Champdany Inds. Ltd."                ""           ""           ""          
"A Infrastructure Ltd."                   ""           ""           ""          
"A J R Infra & Tolling Ltd."              ""           ""           ""          
"A K Capital Services Ltd."               ""           ""           ""          
"A K I India Ltd."                        ""           ""           ""          
"A K M Lace & Embrotex Ltd."              ""           ""           ""          
"A K Spintex Ltd."                        ""           ""           ""          
"A M D Industries Ltd."                   ""           ""           ""          
"A M I Organics Ltd."                     ""           ""           ""          
"A M J Land Holdings Ltd."                "0.2"        "5.2"        "5.29"      
"A M S Polymers Ltd."                     ""           ""           ""          
"A N G Lifesciences India Ltd."           ""           ""           ""          
"A N S Inds. Ltd."                        ""           ""           ""          
"A P L Apollo Tubes Ltd."                 ""           ""           ""          
"A P M Industries Ltd."                   "0.16"       ""           "2.2"       
"A P T Packaging Ltd."                    ""           ""           ""          
"A R C Finance Ltd."                      ""           ""           ""          
"A R S S Infrastructure Projects Ltd."    ""           ""           ""          
"A S I Industries Ltd."                   "0.43"       ""           ""          
"A S M Technologies Ltd."                 "5.11"       "6.7"        "6.47"      
"A T N International Ltd."                ""           "21.1"       "21.15"     
"A T V Projects India Ltd."               ""           "1.15"       "1.25"      
"A U Small Finance Bank Ltd."             ""           ""           ""          
"A V I Polymers Ltd."                     ""           ""           ""          
"A V I Products India Ltd."               "0.91"       ""           ""          
"A V T Natural Products Ltd."             "0.66"       "1.88"       ""          
"A Y M Syntex Ltd."                       "1.66"       ""           ""          
"A-1 Acid Ltd."                           ""           ""           ""          
"Aadhaar Ventures India Ltd."             "2.93"       "8.5"        ""          
"Aadi Industries Ltd."                    ""           ""           "1.25"      
"Aagam Capital Ltd."                      "2.07"       ""           ""          
"Aananda Lakshmi Spg. Mills Ltd."         ""           ""           ""          
"Aanchal Ispat Ltd."                      ""           ""           ""          
"Aar Shyam (India) Invst. Co. Ltd."       ""           ""           ""          
"Aaradhana Realties Ltd."                 ""           ""           ""          
"Aarcon Facilities Ltd."                  ""           ""           ""          
"Aarey Drugs & Pharmaceuticals Ltd."      ""           ""           ""          
"Aarnav Fashions Ltd."                    ""           ""           ""          
"Aartech Solonics Ltd."                   ""           ""           ""          
"Aarti Drugs Ltd."                        "0.83"       ""           ""          
"Aarti Industries Ltd."                   "0.62"       ""           ""          
"Aarti Surfactants Ltd."                  ""           ""           ""          
"Aarvee Denims & Exports Ltd."            "0.13"       ""           ""          
"Aashka Hospitals Ltd."                   ""           ""           ""          
"Aastamangalam Finance Ltd."              "0.81"       ""           ""          
"Aaswa Trading & Exports Ltd."            ""           ""           ""          
"Aavas Financiers Ltd."                   ""           ""           ""          
"Aayush Food & Herbs Ltd."                ""           ""           ""          
"Aban Offshore Ltd."                      ""           "3.77"       "3.78"      
"Abans Enterprises Ltd."                  ""           ""           ""          
"Abate A S Inds. Ltd."                    "0.92"       "4.9"        ""          
"Abbott India Ltd."                       "4.87"       "337.2"      "319.6"     
"Abhayam Trading Ltd."                    ""           "16.2"       "17.25"     
"Abhijit Trading Co. Ltd."                ""           ""           ""          
"Abhinav Capital Services Ltd."           ""           ""           ""          
"Abhinav Leasing & Finance Ltd."          ""           ""           ""          
"Abhishek Corporation Ltd."               ""           ""           ""          
"Abhishek Finlease Ltd."                  ""           ""           ""          
"Abhishek Infraventures Ltd."             ""           ""           ""          
"Abirami Financial Services (India) Ltd." ""           ""           ""          
"Accedere Ltd."                           ""           "10"         "9.95"      
"Accel Ltd."                              "1.81"       "14"         "13.15"     
"Accelya Solutions India Ltd."            "5.59"       "116.8"      "118"       
"Ace Engitech Ltd."                       ""           ""           ""          
"Ace Men Engg. Works Ltd."                ""           ""           ""          
"Ace Software Exports Ltd."               "2.6"        "21.3"       "21.4"      
"Acewin Agriteck Ltd."                    ""           ""           ""          
"Achal Investments Ltd."                  ""           ""           ""          
"Achyut Healthcare Ltd."                  ""           ""           ""          
"Acil Cotton Inds. Ltd."                  "1.28"       ""           ""          
end

[Nick Cox]

Simplifying your data example as below, I note

1. A string identifier stock. which seems simple enough,

2. Three daily dates in observation 1. These seem diagnostic of import of metadata as data, meaning that information in the early rows of a spreadsheet-like file has been read in as data but should be elsewhere.

3. #2 just mentioned seems to be the reason that you have string variables with numeric content.

4. Saving the dates in local macros and applying destring seems to work.

5. Now I don't fully understand as pbratio is evidently for 1 March 2000 or January 3 2000 and its values seem different from those of pr mcap which are for two dates but otherwise seem similar. in magnitude.

I don't work in economics or finance.

* Example generated by -dataex-. For more info, type help dataex
clear
input str54 stock str10(pbratio pr mcap)
""                                "01-03-2000" "01-08-2000" "02-08-2000"
"3M India Ltd."                   "10.96"      "714.25"     "677.35"    
"3P Land Holdings Ltd."           "0.12"       ""           ""          
"63 Moons Technologies Ltd."      "28.89"      "104.25"     "120.9"     
"A B B India Ltd."                "2.46"       "40.73"      "40.59"     
"A B C Gas (International) Ltd."  "4.32"       "2"          "2"         
"A B C India Ltd."                "0.11"       ""           ""          
"A B M Knowledgeware Ltd."        "2.09"       "4.13"       "4.25"      
"A C C Ltd."                      "2.12"       "113.05"     "116.7"     
"A C I Infocom Ltd."              "2.33"       ""           ""          
"A D C India Communications Ltd." "7.24"       "456.95"     "441.1"     
"A D F Foods Ltd."                "0.28"       "7.05"       "7.7"       
"A G I Greenpac Ltd."             "0.89"       "25.8"       ""          
"A M J Land Holdings Ltd."        "0.2"        "5.2"        "5.29"      
"A P M Industries Ltd."           "0.16"       ""           "2.2"       
"A S I Industries Ltd."           "0.43"       ""           ""          
"A S M Technologies Ltd."         "5.11"       "6.7"        "6.47"      
"A T N International Ltd."        ""           "21.1"       "21.15"     
"A T V Projects India Ltd."       ""           "1.15"       "1.25"      
"A V I Products India Ltd."       "0.91"       ""           ""          
"A V T Natural Products Ltd."     "0.66"       "1.88"       ""          
"A Y M Syntex Ltd."               "1.66"       ""           ""          
"Aadhaar Ventures India Ltd."     "2.93"       "8.5"        ""          
"Aadi Industries Ltd."            ""           ""           "1.25"      
"Aagam Capital Ltd."              "2.07"       ""           ""          
"Aarti Drugs Ltd."                "0.83"       ""           ""          
"Aarti Industries Ltd."           "0.62"       ""           ""          
"Aarvee Denims & Exports Ltd."    "0.13"       ""           ""          
"Aastamangalam Finance Ltd."      "0.81"       ""           ""          
"Aban Offshore Ltd."              ""           "3.77"       "3.78"      
"Abate A S Inds. Ltd."            "0.92"       "4.9"        ""          
"Abbott India Ltd."               "4.87"       "337.2"      "319.6"     
"Abhayam Trading Ltd."            ""           "16.2"       "17.25"     
"Accedere Ltd."                   ""           "10"         "9.95"      
"Accel Ltd."                      "1.81"       "14"         "13.15"     
"Accelya Solutions India Ltd."    "5.59"       "116.8"      "118"       
"Ace Software Exports Ltd."       "2.6"        "21.3"       "21.4"      
"Acil Cotton Inds. Ltd."          "1.28"       ""           ""          
end

local label1 = pbratio[1]
local label2 = pr[1]
local label3 = mcap[1]

drop in 1 

destring, replace

list 

 
    +-------------------------------------------------------------+
     |                           stock   pbratio       pr     mcap |
     |-------------------------------------------------------------|
  1. |                   3M India Ltd.     10.96   714.25   677.35 |
  2. |           3P Land Holdings Ltd.       .12        .        . |
  3. |      63 Moons Technologies Ltd.     28.89   104.25    120.9 |
  4. |                A B B India Ltd.      2.46    40.73    40.59 |
  5. |  A B C Gas (International) Ltd.      4.32        2        2 |
     |-------------------------------------------------------------|
  6. |                A B C India Ltd.       .11        .        . |
  7. |        A B M Knowledgeware Ltd.      2.09     4.13     4.25 |
  8. |                      A C C Ltd.      2.12   113.05    116.7 |
  9. |              A C I Infocom Ltd.      2.33        .        . |
 10. | A D C India Communications Ltd.      7.24   456.95    441.1 |
     |-------------------------------------------------------------|
 11. |                A D F Foods Ltd.       .28     7.05      7.7 |
 12. |             A G I Greenpac Ltd.       .89     25.8        . |
 13. |        A M J Land Holdings Ltd.        .2      5.2     5.29 |
 14. |           A P M Industries Ltd.       .16        .      2.2 |
 15. |           A S I Industries Ltd.       .43        .        . |
     |-------------------------------------------------------------|
 16. |         A S M Technologies Ltd.      5.11      6.7     6.47 |
 17. |        A T N International Ltd.         .     21.1    21.15 |
 18. |       A T V Projects India Ltd.         .     1.15     1.25 |
 19. |       A V I Products India Ltd.       .91        .        . |
 20. |     A V T Natural Products Ltd.       .66     1.88        . |
     |-------------------------------------------------------------|
 21. |               A Y M Syntex Ltd.      1.66        .        . |
 22. |     Aadhaar Ventures India Ltd.      2.93      8.5        . |
 23. |            Aadi Industries Ltd.         .        .     1.25 |
 24. |              Aagam Capital Ltd.      2.07        .        . |
 25. |                Aarti Drugs Ltd.       .83        .        . |
     |-------------------------------------------------------------|
 26. |           Aarti Industries Ltd.       .62        .        . |
 27. |    Aarvee Denims & Exports Ltd.       .13        .        . |
 28. |      Aastamangalam Finance Ltd.       .81        .        . |
 29. |              Aban Offshore Ltd.         .     3.77     3.78 |
 30. |            Abate A S Inds. Ltd.       .92      4.9        . |
     |-------------------------------------------------------------|
 31. |               Abbott India Ltd.      4.87    337.2    319.6 |
 32. |            Abhayam Trading Ltd.         .     16.2    17.25 |
 33. |                   Accedere Ltd.         .       10     9.95 |
 34. |                      Accel Ltd.      1.81       14    13.15 |
 35. |    Accelya Solutions India Ltd.      5.59    116.8      118 |
     |-------------------------------------------------------------|
 36. |       Ace Software Exports Ltd.       2.6     21.3     21.4 |
 37. |          Acil Cotton Inds. Ltd.      1.28        .        . |
     +-------------------------------------------------------------+

[Sartaj Hussain]

Thanks Nick, But i require two more data columns to appear and they are stock_id and date.

[Leonardo Guizzetti]

The principles of Nick's post still apply to your larger dataset, however you haven't posted stock ID or stock date. The ID is easy enough to use asking with her stock name as you identifying variables. Why not give it a try yourself now with your data? Maybe subset your data to just 3 or 4 stocks as a test set to make sure you understand what is happening and how to do it.

[Sartaj Hussain]

Date is given in the first row of data example. I want the data to be structured into panel using following general command:

egen stock_id=group(stock)
reshape long pr mcap pbratio, i(stock_id) j(date)

But i confront problems with respect to date. Hope someone understands this problem in right perspective.

[Clyde Schechter]

I am as puzzled by the organization of this data set as are the previous responders.

If we extract the numeric values found in all but the first observations of this data set, we can see that pbratio is of a completely different scale from pr and mcap. So these really are differenvariables and represent different things.

The mystery is what connection the dates in the first observation have with the rest of the data. It is already the case that each stock appears just once in the data (at least that is true of the example.) To make this look like panel data would require creating additional observations for each stock corresponding to the values of pr mcap and pbratio at different points in time. But the data only contain one value of pr, mcap, and pbratio for each stock. The three dates shown in the first observation have no clear connection to anything else in this data set.

Does your source data set contain more colulmns, headed by additional dates in the first row and containing additional values of pr mcap and pbratio? If so, those need to be included in the example in order to -reshape- it. If not, I really don't know what to make of this data set: the dates seem to be unconnected to anything else in it.

[Sartaj Hussain]

The actual dataset has three columns for pr, mcap and pbratio, but there are more columns headed by additional dates for pr only. mcap and pbratio for each cross section has values in the month March and August only. I append another dataset with more pr data columns. v1 is the pbratio, v23 is mcap, v2-v10 are pr values.

* Example generated by -dataex-. For more info, type help dataex
clear
input str54 v1 str10(v2 v3 v4 v5 v6 v7 v8 v9 v23)
"Company Name"                            "31-03-2022" "01-08-2022" "02-08-2022" "03-08-2022" "04-08-2022" "05-08-2022" "08-08-2022" "10-08-2022" "30-08-2022"
""                                        "pbratio"    "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "mcap"      
"20 Microns Ltd."                         "1.12"       "103.25"     "99.65"      "97.95"      "97.55"      "108.25"     "111.25"     "108.8"      "387.8"    
"3I Infotech Ltd."                        "1.07"       "41.7"       "42.25"      "40.75"      "40.1"       "40.85"      "41.35"      "40.9"       "744.28"    
"3M India Ltd."                           "8.9"        "22908.4"    "22845.25"   "22724.55"   "23401.1"    "23658.5"    "23667.35"   "22776.8"    "26228.29"  
"3P Land Holdings Ltd."                   "0.38"       "15.6"       "15.15"      "15.2"       "15.1"       "15.1"       "15.15"      "15.4"       "30.69"    
"52 Weeks Entertainment Ltd."             "0.62"       "2.33"       "2.24"       "2.24"       "2.16"       "2.19"       "2.19"       "2.09"       "7.01"      
"5Paisa Capital Ltd."                     "2.8"        "283.75"     "285.7"      "284.3"      "283.8"      "280.75"     "279.9"      "279.55"     "1054.29"  
"63 Moons Technologies Ltd."              "0.48"       "190.9"      "192.25"     "187.65"     "197"        "188.05"     "187"        "185.9"      "840.24"    
"7Nr Retail Ltd."                         "2.13"       "1.53"       "1.54"       "1.57"       "1.6"        "1.74"       "1.63"       "1.64"       "42.2"      
"7Seas Entertainment Ltd."                ""           "17.95"      "17.85"      "17.55"      "17.5"       "18.45"      "18.95"      "18"         "30.88"    
"A & M Febcon Ltd."                       "0.14"       "0.96"       ""           ""           ""           ""           "0.92"       ""           "1.17"      
"A 2 Z Infra Engg. Ltd."                  "1.59"       "12.41"      "13.03"      "13.58"      "14.06"      "13.94"      "13.58"      "12.8"       "217.16"    
"A A Plus Tradelink Ltd."                 ""           ""           ""           ""           ""           ""           ""           ""           "5.63"      
"A A R Commercial Co. Ltd. [Merged]"      ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A A R V Infratel Ltd."                   ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A B B India Ltd."                        "10.33"      "2746.2"     "2756.65"    "2670.3"     "2667.9"     "2686.95"    "2718.15"    "2771.25"    "69173.25"  
"A B C Gas (International) Ltd."          "2.15"       "50.55"      "53.05"      "55.7"       "58.45"      "61.35"      "64.4"       "67.6"       "25.02"    
"A B C India Ltd."                        "1.12"       "76.5"       "73.6"       "75.85"      "75.65"      "75.05"      "77.2"       "74.1"       "40.36"    
"A B M Knowledgeware Ltd."                "1.07"       "87.95"      "89.95"      "86"         "91.55"      "94.8"       "94.75"      "93.4"       "181.72"    
"A C C Ltd."                              "2.77"       "2226.2"     "2226.3"     "2235.15"    "2218.25"    "2254.9"     "2223.7"     "2227.2"     "43180.74"  
"A C E Edutrend Ltd."                     "0.17"       ""           ""           ""           ""           ""           "2.51"       ""           "2.31"      
"A C I Infocom Ltd."                      "1.05"       "1.39"       "1.45"       "1.52"       "1.56"       "1.53"       "1.46"       "1.51"       "14.81"    
"A D C India Communications Ltd."         "2.78"       "336.2"      "333.55"     "336"        "336.5"      "327.95"     "328.15"     "329.65"     "167.1"    
"A D F Foods Ltd."                        "4.78"       "703.7"      "692.2"      "727.85"     "739.1"      "719.7"      "713.65"     "698.55"     "1596.87"  
"A D S Diagnostic Ltd."                   ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A F Enterprises Ltd."                    "8.65"       "72.7"       "69.1"       "65.65"      "62.4"       "59.3"       "56.35"      "53.55"      "35.84"    
"A G I Greenpac Ltd."                     "1.38"       "275.8"      "265.7"      "279.5"      "271.7"      "272.95"     "272.7"      "275.95"     "2030.53"  
"A G I Infra Ltd."                        "2.3"        "592.95"     "567.1"      "574.8"      "553.7"      "546.4"      "522.75"     "505.85"     "579.5"    
"A G S Transact Technologies Ltd."        "2.19"       "82.85"      "83.3"       "84.75"      "84.9"       "85.4"       "90.95"      "87.35"      "1054.39"  
"A I A Engineering Ltd."                  "3.34"       "2476.2"     "2492.05"    "2491"       "2438"       "2370.25"    "2395.9"     "2583.05"    "24557.72"  
"A I Champdany Inds. Ltd."                "1.29"       "17.85"      "18.7"       "19.6"       "20.55"      "21.55"      "21.35"      "20.3"       "58.12"    
"A Infrastructure Ltd."                   "3.7"        "18.75"      "19.2"       "18.6"       "18.3"       "18.55"      "18.4"       "19.3"       "163.33"    
"A J R Infra & Tolling Ltd."              "0.58"       "1.4"        "1.47"       "1.53"       "1.52"       "1.56"       "1.58"       "1.54"       "137.51"    
"A K Capital Services Ltd."               "0.66"       "431.6"      "434.95"     "431.2"      "428"        "426.55"     "437.65"     "426"        "286.37"    
"A K I India Ltd."                        "2.71"       "67.15"      "65.15"      "61.9"       ""           "61.9"       "58.85"      ""           "115.12"    
"A K M Lace & Embrotex Ltd."              "1.12"       ""           ""           ""           ""           ""           ""           ""           "10.83"    
"A K Spintex Ltd."                        "1.38"       "48.85"      "48.95"      "49"         "48.45"      "49.15"      "50.25"      "51.55"      "31.37"    
"A M D Industries Ltd."                   "0.63"       "46.4"       "48.55"      "57.9"       "54.35"      "55.6"       "56.1"       "55.9"       "160.81"    
"A M I Organics Ltd."                     "7.03"       "1041.6"     "1035.35"    "1009.4"     "1026.45"    "1049.45"    "1045.95"    "1000.7"     "3616.92"  
"A M J Land Holdings Ltd."                "0.99"       "26.35"      "25.75"      "25.75"      "26.05"      "26"         "26.15"      "25.95"      "134.48"    
"A M S Polymers Ltd."                     ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A N G Lifesciences India Ltd."           "3.03"       "170.5"      "169.45"     "169.4"      "172.35"     "171.95"     "164.1"      "162.2"      "188.54"    
"A N S Inds. Ltd."                        "1.42"       "11.4"       "11"         "11"         "11.55"      ""           "12.1"       "12.1"       "12.12"    
"A P L Apollo Tubes Ltd."                 "13.11"      "985"        "984.3"      "980.6"      "1051.95"    "1030.35"    "1022.95"    "1051.15"    "23695.31"  
"A P M Industries Ltd."                   "0.55"       "48.5"       "54.25"      "52.25"      "50"         "50.45"      "49.45"      "47.5"       "113.46"    
"A P T Packaging Ltd."                    ""           "31.05"      "32.6"       ""           ""           "34.2"       "35.9"       "37.65"      "32.11"    
"A R C Finance Ltd."                      "2.71"       "1.22"       "1.16"       "1.11"       "1.16"       "1.21"       "1.21"       "1.17"       "52.01"    
"A R S S Infrastructure Projects Ltd."    ""           "22.4"       "23.3"       "23.2"       "23.2"       "22.95"      "22.75"      "21.65"      "46.61"    
"A S I Industries Ltd."                   "0.61"       "13.6"       "13.65"      "14.15"      "14.05"      "14.1"       "13.75"      "13.56"      "126.19"    
"A S M Technologies Ltd."                 "8.66"       "455.8"      "455.8"      "478.55"     "502.15"     "552.35"     "607.55"     "579.55"     "643.72"    
"A T N International Ltd."                ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A T V Projects India Ltd."               "0.28"       "9.51"       "9.87"       "9.73"       "9.83"       "9.81"       "9.73"       "9.76"       "50.25"    
"A U Small Finance Bank Ltd."             "5.22"       "604.9"      "618.15"     "609.35"     "637"        "647.6"      "650.15"     "644.15"     "42177.19"  
"A V I Polymers Ltd."                     "3.02"       "23.95"      "24.3"       "24.05"      "23.75"      "23.75"      "23.5"       "23.15"      "9.06"      
"A V I Products India Ltd."               "2.51"       ""           "26.5"       "26.5"       ""           "26.5"       ""           ""           "6.86"      
"A V T Natural Products Ltd."             "5.23"       "92.5"       "91"         "89.5"       "90.35"      "92.05"      "90.95"      "91.35"      "1610.4"    
"A Y M Syntex Ltd."                       "1.37"       "85.35"      "84.8"       "83.55"      "83.65"      "84.25"      "85"         "83.05"      "460.71"    
"A-1 Acid Ltd."                           "6.45"       "312.65"     "312.8"      "314.15"     "315"        "311.7"      "313.75"     "314.75"     "363.34"    
"Aadhaar Ventures India Ltd."             ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aadi Industries Ltd."                    ""           "4.42"       "4.64"       "4.87"       "5"          "5"          "5.25"       "5"          "4.34"      
"Aagam Capital Ltd."                      ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aananda Lakshmi Spg. Mills Ltd."         ""           "10"         "9.95"       "9"          "9.5"        "9.49"       "9.45"       "9"          "3.15"      
"Aanchal Ispat Ltd."                      "1.41"       "12.01"      "12.15"      "12"         "12.18"      "11.84"      "12"         "11.61"      "29.84"    
"Aar Shyam (India) Invst. Co. Ltd."       ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aaradhana Realties Ltd."                 ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aarcon Facilities Ltd."                  ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aarey Drugs & Pharmaceuticals Ltd."      "0.94"       "35.9"       "36.6"       "36.25"      "35.4"       "35.2"       "34.5"       "35.1"       "98.11"    
"Aarnav Fashions Ltd."                    "3.54"       "37.3"       "38.45"      "39.55"      "38.25"      "38.4"       "38"         "38.2"       "80.65"    
"Aartech Solonics Ltd."                   ""           ""           "29.15"      ""           "30.6"       ""           ""           ""           "37.44"    
"Aarti Drugs Ltd."                        "4.09"       "429.4"      "415.25"     "416.9"      "432.55"     "426.8"      "424.85"     "424.65"     "3997.08"  
"Aarti Industries Ltd."                   "5.99"       "802.3"      "811.45"     "811.1"      "809"        "814.1"      "803.85"     "819.05"     "30107.77"  
"Aarti Surfactants Ltd."                  "4.19"       "772.95"     "813.2"      "817.5"      "806.95"     "769.1"      "775"        "763.9"      "559.47"    
"Aarvee Denims & Exports Ltd."            "0.44"       "22.3"       "21.8"       "21.95"      "21.1"       "21.8"       "21.9"       "21.1"       "66.51"    
"Aashka Hospitals Ltd."                   "1.16"       "38.25"      "38.1"       "37.6"       "39.5"       "39.5"       "38.9"       "38.1"       "90.21"    
"Aastamangalam Finance Ltd."              "0.41"       "11.34"      "11.12"      "11.18"      "11.86"      "11.36"      "11.3"       "12"         "4.71"      
"Aaswa Trading & Exports Ltd."            ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aavas Financiers Ltd."                   "7.21"       "2394.85"    "2382.35"    "2309.55"    "2259.2"     "2228.95"    "2162.85"    "2210.3"     "17976.62"  
"Aayush Food & Herbs Ltd."                "1.81"       "22.65"      "21.95"      "21.95"      "21.6"       "22"         "22"         "21.3"       "7.17"      
"Aban Offshore Ltd."                      ""           "45.3"       "45.55"      "45.6"       "45.2"       "44.65"      "46"         "45.3"       "337.64"    
"Abans Enterprises Ltd."                  "7.79"       "115.05"     "119.75"     "113.05"     "113.45"     "111.2"      "109.4"      "107.2"      "143.68"    
"Abate A S Inds. Ltd."                    "16.83"      "8.63"       "8.2"        "7.79"       "8.17"       "7.77"       "8.15"       "8.55"       "6.62"      
"Abbott India Ltd."                       "13.34"      "20541.95"   "20531.8"    "20629.55"   "20420.95"   "20395.55"   "20292.2"    "19608.2"    "39847.86"  
"Abhayam Trading Ltd."                    ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Abhijit Trading Co. Ltd."                ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Abhinav Capital Services Ltd."           "0.74"       "94.5"       "97.8"       "93.4"       "92.45"      "87.85"      "89.8"       "85.9"       "71.32"    
"Abhinav Leasing & Finance Ltd."          "2.42"       "1.95"       "2"          "1.95"       "1.92"       "1.95"       "1.92"       "1.95"       "9.7"      
"Abhishek Corporation Ltd."               ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Abhishek Finlease Ltd."                  "4.45"       ""           ""           "39.85"      ""           "39"         ""           "38"         "13.05"    
"Abhishek Infraventures Ltd."             "0.55"       "3.88"       ""           "3.88"       "3.88"       "3.88"       "3.69"       "3.69"       "1.35"      
"Abirami Financial Services (India) Ltd." "0.41"       "6.47"       ""           ""           ""           ""           "6.79"       ""           "4.07"      
"Accedere Ltd."                           "4.69"       "30.65"      "29.25"      "30"         "31"         "31.95"      "30.4"       "28.9"       "14.33"    
"Accel Ltd."                              "3.21"       "18.45"      "18.4"       "18"         "18.25"      "18.1"       "18"         "18.05"      "100.31"    
"Accelya Solutions India Ltd."            "6.05"       "958.7"      "979.85"     "1061.6"     "1084.05"    "1088.2"     "1066.7"     "1050.6"     "1681.67"  
"Ace Engitech Ltd."                       "5.17"       "15.06"      "14.32"      "14.33"      "13.63"      "13.63"      "13.5"       "14.17"      "4.86"      
"Ace Men Engg. Works Ltd."                "3.56"       "61.8"       "63"         "63.75"      "62"         "58.9"       "57.2"       "58.45"      "16.03"    
"Ace Software Exports Ltd."               "0.51"       "16.25"      "15.55"      "16.45"      "14.85"      ""           "14.85"      "15.55"      "8.49"      
"Acewin Agriteck Ltd."                    "0.18"       "3.63"       ""           ""           ""           ""           "3.45"       ""           "3.36"      
"Achal Investments Ltd."                  ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Achyut Healthcare Ltd."                  "1.53"       ""           ""           ""           ""           ""           "18"         ""           "13.14"    
end

This is the format that i want:

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str54 stock float(date pr mcap pbratio)
1 "20 Microns Ltd."             22735        .        . 1.12
1 "20 Microns Ltd."             22858   103.25        .    .
1 "20 Microns Ltd."             22859    99.65        .    .
1 "20 Microns Ltd."             22860    97.95        .    .
1 "20 Microns Ltd."             22861    97.55        .    .
1 "20 Microns Ltd."             22862   108.25        .    .
1 "20 Microns Ltd."             22865   111.25        .    .
1 "20 Microns Ltd."             22867    108.8        .    .
1 "20 Microns Ltd."             22868   105.45        .    .
1 "20 Microns Ltd."             22869   105.85        .    .
1 "20 Microns Ltd."             22873   108.15        .    .
1 "20 Microns Ltd."             22874   109.65        .    .
1 "20 Microns Ltd."             22875   105.05        .    .
1 "20 Microns Ltd."             22876   102.25        .    .
1 "20 Microns Ltd."             22879    107.4        .    .
1 "20 Microns Ltd."             22880    108.6        .    .
1 "20 Microns Ltd."             22881      115        .    .
1 "20 Microns Ltd."             22882   111.75        .    .
1 "20 Microns Ltd."             22883    107.6        .    .
1 "20 Microns Ltd."             22886   108.05        .    .
1 "20 Microns Ltd."             22887    109.9    387.8    .
2 "3I Infotech Ltd."            22735        .        . 1.07
2 "3I Infotech Ltd."            22858     41.7        .    .
2 "3I Infotech Ltd."            22859    42.25        .    .
2 "3I Infotech Ltd."            22860    40.75        .    .
2 "3I Infotech Ltd."            22861     40.1        .    .
2 "3I Infotech Ltd."            22862    40.85        .    .
2 "3I Infotech Ltd."            22865    41.35        .    .
2 "3I Infotech Ltd."            22867     40.9        .    .
2 "3I Infotech Ltd."            22868    40.85        .    .
2 "3I Infotech Ltd."            22869     40.7        .    .
2 "3I Infotech Ltd."            22873    45.65        .    .
2 "3I Infotech Ltd."            22874     44.8        .    .
2 "3I Infotech Ltd."            22875     43.6        .    .
2 "3I Infotech Ltd."            22876     45.9        .    .
2 "3I Infotech Ltd."            22879     44.1        .    .
2 "3I Infotech Ltd."            22880    44.55        .    .
2 "3I Infotech Ltd."            22881    45.15        .    .
2 "3I Infotech Ltd."            22882    44.65        .    .
2 "3I Infotech Ltd."            22883    44.45        .    .
2 "3I Infotech Ltd."            22886     43.2        .    .
2 "3I Infotech Ltd."            22887     44.2   744.28    .
3 "3M India Ltd."               22735        .        .  8.9
3 "3M India Ltd."               22858  22908.4        .    .
3 "3M India Ltd."               22859 22845.25        .    .
3 "3M India Ltd."               22860 22724.55        .    .
3 "3M India Ltd."               22861  23401.1        .    .
3 "3M India Ltd."               22862  23658.5        .    .
3 "3M India Ltd."               22865 23667.35        .    .
3 "3M India Ltd."               22867  22776.8        .    .
3 "3M India Ltd."               22868  22781.8        .    .
3 "3M India Ltd."               22869  22436.2        .    .
3 "3M India Ltd."               22873  22408.6        .    .
3 "3M India Ltd."               22874  22356.1        .    .
3 "3M India Ltd."               22875    23259        .    .
3 "3M India Ltd."               22876  22920.9        .    .
3 "3M India Ltd."               22879  22350.9        .    .
3 "3M India Ltd."               22880 22354.95        .    .
3 "3M India Ltd."               22881 22404.45        .    .
3 "3M India Ltd."               22882 22816.15        .    .
3 "3M India Ltd."               22883    23111        .    .
3 "3M India Ltd."               22886  23238.7        .    .
3 "3M India Ltd."               22887 23282.85 26228.29    .
4 "3P Land Holdings Ltd."       22735        .        .  .38
4 "3P Land Holdings Ltd."       22858     15.6        .    .
4 "3P Land Holdings Ltd."       22859    15.15        .    .
4 "3P Land Holdings Ltd."       22860     15.2        .    .
4 "3P Land Holdings Ltd."       22861     15.1        .    .
4 "3P Land Holdings Ltd."       22862     15.1        .    .
4 "3P Land Holdings Ltd."       22865    15.15        .    .
4 "3P Land Holdings Ltd."       22867     15.4        .    .
4 "3P Land Holdings Ltd."       22868    15.15        .    .
4 "3P Land Holdings Ltd."       22869     15.2        .    .
4 "3P Land Holdings Ltd."       22873       15        .    .
4 "3P Land Holdings Ltd."       22874       15        .    .
4 "3P Land Holdings Ltd."       22875     16.4        .    .
4 "3P Land Holdings Ltd."       22876    16.85        .    .
4 "3P Land Holdings Ltd."       22879    18.15        .    .
4 "3P Land Holdings Ltd."       22880    19.65        .    .
4 "3P Land Holdings Ltd."       22881    18.85        .    .
4 "3P Land Holdings Ltd."       22882    18.25        .    .
4 "3P Land Holdings Ltd."       22883    17.85        .    .
4 "3P Land Holdings Ltd."       22886    17.15        .    .
4 "3P Land Holdings Ltd."       22887    17.05    30.69    .
5 "52 Weeks Entertainment Ltd." 22735        .        .  .62
5 "52 Weeks Entertainment Ltd." 22858     2.33        .    .
5 "52 Weeks Entertainment Ltd." 22859     2.24        .    .
5 "52 Weeks Entertainment Ltd." 22860     2.24        .    .
5 "52 Weeks Entertainment Ltd." 22861     2.16        .    .
5 "52 Weeks Entertainment Ltd." 22862     2.19        .    .
5 "52 Weeks Entertainment Ltd." 22865     2.19        .    .
5 "52 Weeks Entertainment Ltd." 22867     2.09        .    .
5 "52 Weeks Entertainment Ltd." 22868     2.17        .    .
5 "52 Weeks Entertainment Ltd." 22869     2.11        .    .
5 "52 Weeks Entertainment Ltd." 22873     2.08        .    .
5 "52 Weeks Entertainment Ltd." 22874     1.99        .    .
5 "52 Weeks Entertainment Ltd." 22875     2.01        .    .
5 "52 Weeks Entertainment Ltd." 22876     2.06        .    .
5 "52 Weeks Entertainment Ltd." 22879     1.96        .    .
5 "52 Weeks Entertainment Ltd." 22880     1.95        .    .
end
format %td date

[Clyde Schechter]

I'm still not entirely sure how this works given the daily data on pr and the very sporadic data on pbratio and mcap. But this may at least be a start in the right direction:

rename v1 company_name
ds company_name, not
local headings `r(varlist)'
local newnames
foreach h of local headings {
    local date = (daily(`h'[1], "DMY"))
    local prefix = `h'[2]
    display `"`prefix'"', `date'
    local newnames `newnames' `prefix'`date'
}
rename (`headings') (`newnames')
drop in 1/2
reshape long pbratio pr mcap, i(company_name) j(date)
format date %td
destring mcap pbratio pr, replace
format mcap pbratio pr %3.2f

[Sartaj Hussain]

Well, i need it to do two separate sorts on yearly basis to create groups of pr. First sort on mcap to create two groups and second sort on pbratio to create three. And then then intersection of mcap and pbratio groups to create six groups. In past we did it once.

[Clyde Schechter]

OK, so the whole thing should be something like this:

* Example generated by -dataex-. For more info, type help dataex
clear*
input str54 v1 str10(v2 v3 v4 v5 v6 v7 v8 v9 v23)
"Company Name"                            "31-03-2022" "01-08-2022" "02-08-2022" "03-08-2022" "04-08-2022" "05-08-2022" "08-08-2022" "10-08-2022" "30-08-2022"
""                                        "pbratio"    "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "pr"         "mcap"      
"20 Microns Ltd."                         "1.12"       "103.25"     "99.65"      "97.95"      "97.55"      "108.25"     "111.25"     "108.8"      "387.8"    
"3I Infotech Ltd."                        "1.07"       "41.7"       "42.25"      "40.75"      "40.1"       "40.85"      "41.35"      "40.9"       "744.28"    
"3M India Ltd."                           "8.9"        "22908.4"    "22845.25"   "22724.55"   "23401.1"    "23658.5"    "23667.35"   "22776.8"    "26228.29"  
"3P Land Holdings Ltd."                   "0.38"       "15.6"       "15.15"      "15.2"       "15.1"       "15.1"       "15.15"      "15.4"       "30.69"    
"52 Weeks Entertainment Ltd."             "0.62"       "2.33"       "2.24"       "2.24"       "2.16"       "2.19"       "2.19"       "2.09"       "7.01"      
"5Paisa Capital Ltd."                     "2.8"        "283.75"     "285.7"      "284.3"      "283.8"      "280.75"     "279.9"      "279.55"     "1054.29"  
"63 Moons Technologies Ltd."              "0.48"       "190.9"      "192.25"     "187.65"     "197"        "188.05"     "187"        "185.9"      "840.24"    
"7Nr Retail Ltd."                         "2.13"       "1.53"       "1.54"       "1.57"       "1.6"        "1.74"       "1.63"       "1.64"       "42.2"      
"7Seas Entertainment Ltd."                ""           "17.95"      "17.85"      "17.55"      "17.5"       "18.45"      "18.95"      "18"         "30.88"    
"A & M Febcon Ltd."                       "0.14"       "0.96"       ""           ""           ""           ""           "0.92"       ""           "1.17"      
"A 2 Z Infra Engg. Ltd."                  "1.59"       "12.41"      "13.03"      "13.58"      "14.06"      "13.94"      "13.58"      "12.8"       "217.16"    
"A A Plus Tradelink Ltd."                 ""           ""           ""           ""           ""           ""           ""           ""           "5.63"      
"A A R Commercial Co. Ltd. [Merged]"      ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A A R V Infratel Ltd."                   ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A B B India Ltd."                        "10.33"      "2746.2"     "2756.65"    "2670.3"     "2667.9"     "2686.95"    "2718.15"    "2771.25"    "69173.25"  
"A B C Gas (International) Ltd."          "2.15"       "50.55"      "53.05"      "55.7"       "58.45"      "61.35"      "64.4"       "67.6"       "25.02"    
"A B C India Ltd."                        "1.12"       "76.5"       "73.6"       "75.85"      "75.65"      "75.05"      "77.2"       "74.1"       "40.36"    
"A B M Knowledgeware Ltd."                "1.07"       "87.95"      "89.95"      "86"         "91.55"      "94.8"       "94.75"      "93.4"       "181.72"    
"A C C Ltd."                              "2.77"       "2226.2"     "2226.3"     "2235.15"    "2218.25"    "2254.9"     "2223.7"     "2227.2"     "43180.74"  
"A C E Edutrend Ltd."                     "0.17"       ""           ""           ""           ""           ""           "2.51"       ""           "2.31"      
"A C I Infocom Ltd."                      "1.05"       "1.39"       "1.45"       "1.52"       "1.56"       "1.53"       "1.46"       "1.51"       "14.81"    
"A D C India Communications Ltd."         "2.78"       "336.2"      "333.55"     "336"        "336.5"      "327.95"     "328.15"     "329.65"     "167.1"    
"A D F Foods Ltd."                        "4.78"       "703.7"      "692.2"      "727.85"     "739.1"      "719.7"      "713.65"     "698.55"     "1596.87"  
"A D S Diagnostic Ltd."                   ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A F Enterprises Ltd."                    "8.65"       "72.7"       "69.1"       "65.65"      "62.4"       "59.3"       "56.35"      "53.55"      "35.84"    
"A G I Greenpac Ltd."                     "1.38"       "275.8"      "265.7"      "279.5"      "271.7"      "272.95"     "272.7"      "275.95"     "2030.53"  
"A G I Infra Ltd."                        "2.3"        "592.95"     "567.1"      "574.8"      "553.7"      "546.4"      "522.75"     "505.85"     "579.5"    
"A G S Transact Technologies Ltd."        "2.19"       "82.85"      "83.3"       "84.75"      "84.9"       "85.4"       "90.95"      "87.35"      "1054.39"  
"A I A Engineering Ltd."                  "3.34"       "2476.2"     "2492.05"    "2491"       "2438"       "2370.25"    "2395.9"     "2583.05"    "24557.72"  
"A I Champdany Inds. Ltd."                "1.29"       "17.85"      "18.7"       "19.6"       "20.55"      "21.55"      "21.35"      "20.3"       "58.12"    
"A Infrastructure Ltd."                   "3.7"        "18.75"      "19.2"       "18.6"       "18.3"       "18.55"      "18.4"       "19.3"       "163.33"    
"A J R Infra & Tolling Ltd."              "0.58"       "1.4"        "1.47"       "1.53"       "1.52"       "1.56"       "1.58"       "1.54"       "137.51"    
"A K Capital Services Ltd."               "0.66"       "431.6"      "434.95"     "431.2"      "428"        "426.55"     "437.65"     "426"        "286.37"    
"A K I India Ltd."                        "2.71"       "67.15"      "65.15"      "61.9"       ""           "61.9"       "58.85"      ""           "115.12"    
"A K M Lace & Embrotex Ltd."              "1.12"       ""           ""           ""           ""           ""           ""           ""           "10.83"    
"A K Spintex Ltd."                        "1.38"       "48.85"      "48.95"      "49"         "48.45"      "49.15"      "50.25"      "51.55"      "31.37"    
"A M D Industries Ltd."                   "0.63"       "46.4"       "48.55"      "57.9"       "54.35"      "55.6"       "56.1"       "55.9"       "160.81"    
"A M I Organics Ltd."                     "7.03"       "1041.6"     "1035.35"    "1009.4"     "1026.45"    "1049.45"    "1045.95"    "1000.7"     "3616.92"  
"A M J Land Holdings Ltd."                "0.99"       "26.35"      "25.75"      "25.75"      "26.05"      "26"         "26.15"      "25.95"      "134.48"    
"A M S Polymers Ltd."                     ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A N G Lifesciences India Ltd."           "3.03"       "170.5"      "169.45"     "169.4"      "172.35"     "171.95"     "164.1"      "162.2"      "188.54"    
"A N S Inds. Ltd."                        "1.42"       "11.4"       "11"         "11"         "11.55"      ""           "12.1"       "12.1"       "12.12"    
"A P L Apollo Tubes Ltd."                 "13.11"      "985"        "984.3"      "980.6"      "1051.95"    "1030.35"    "1022.95"    "1051.15"    "23695.31"  
"A P M Industries Ltd."                   "0.55"       "48.5"       "54.25"      "52.25"      "50"         "50.45"      "49.45"      "47.5"       "113.46"    
"A P T Packaging Ltd."                    ""           "31.05"      "32.6"       ""           ""           "34.2"       "35.9"       "37.65"      "32.11"    
"A R C Finance Ltd."                      "2.71"       "1.22"       "1.16"       "1.11"       "1.16"       "1.21"       "1.21"       "1.17"       "52.01"    
"A R S S Infrastructure Projects Ltd."    ""           "22.4"       "23.3"       "23.2"       "23.2"       "22.95"      "22.75"      "21.65"      "46.61"    
"A S I Industries Ltd."                   "0.61"       "13.6"       "13.65"      "14.15"      "14.05"      "14.1"       "13.75"      "13.56"      "126.19"    
"A S M Technologies Ltd."                 "8.66"       "455.8"      "455.8"      "478.55"     "502.15"     "552.35"     "607.55"     "579.55"     "643.72"    
"A T N International Ltd."                ""           ""           ""           ""           ""           ""           ""           ""           ""          
"A T V Projects India Ltd."               "0.28"       "9.51"       "9.87"       "9.73"       "9.83"       "9.81"       "9.73"       "9.76"       "50.25"    
"A U Small Finance Bank Ltd."             "5.22"       "604.9"      "618.15"     "609.35"     "637"        "647.6"      "650.15"     "644.15"     "42177.19"  
"A V I Polymers Ltd."                     "3.02"       "23.95"      "24.3"       "24.05"      "23.75"      "23.75"      "23.5"       "23.15"      "9.06"      
"A V I Products India Ltd."               "2.51"       ""           "26.5"       "26.5"       ""           "26.5"       ""           ""           "6.86"      
"A V T Natural Products Ltd."             "5.23"       "92.5"       "91"         "89.5"       "90.35"      "92.05"      "90.95"      "91.35"      "1610.4"    
"A Y M Syntex Ltd."                       "1.37"       "85.35"      "84.8"       "83.55"      "83.65"      "84.25"      "85"         "83.05"      "460.71"    
"A-1 Acid Ltd."                           "6.45"       "312.65"     "312.8"      "314.15"     "315"        "311.7"      "313.75"     "314.75"     "363.34"    
"Aadhaar Ventures India Ltd."             ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aadi Industries Ltd."                    ""           "4.42"       "4.64"       "4.87"       "5"          "5"          "5.25"       "5"          "4.34"      
"Aagam Capital Ltd."                      ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aananda Lakshmi Spg. Mills Ltd."         ""           "10"         "9.95"       "9"          "9.5"        "9.49"       "9.45"       "9"          "3.15"      
"Aanchal Ispat Ltd."                      "1.41"       "12.01"      "12.15"      "12"         "12.18"      "11.84"      "12"         "11.61"      "29.84"    
"Aar Shyam (India) Invst. Co. Ltd."       ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aaradhana Realties Ltd."                 ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aarcon Facilities Ltd."                  ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aarey Drugs & Pharmaceuticals Ltd."      "0.94"       "35.9"       "36.6"       "36.25"      "35.4"       "35.2"       "34.5"       "35.1"       "98.11"    
"Aarnav Fashions Ltd."                    "3.54"       "37.3"       "38.45"      "39.55"      "38.25"      "38.4"       "38"         "38.2"       "80.65"    
"Aartech Solonics Ltd."                   ""           ""           "29.15"      ""           "30.6"       ""           ""           ""           "37.44"    
"Aarti Drugs Ltd."                        "4.09"       "429.4"      "415.25"     "416.9"      "432.55"     "426.8"      "424.85"     "424.65"     "3997.08"  
"Aarti Industries Ltd."                   "5.99"       "802.3"      "811.45"     "811.1"      "809"        "814.1"      "803.85"     "819.05"     "30107.77"  
"Aarti Surfactants Ltd."                  "4.19"       "772.95"     "813.2"      "817.5"      "806.95"     "769.1"      "775"        "763.9"      "559.47"    
"Aarvee Denims & Exports Ltd."            "0.44"       "22.3"       "21.8"       "21.95"      "21.1"       "21.8"       "21.9"       "21.1"       "66.51"    
"Aashka Hospitals Ltd."                   "1.16"       "38.25"      "38.1"       "37.6"       "39.5"       "39.5"       "38.9"       "38.1"       "90.21"    
"Aastamangalam Finance Ltd."              "0.41"       "11.34"      "11.12"      "11.18"      "11.86"      "11.36"      "11.3"       "12"         "4.71"      
"Aaswa Trading & Exports Ltd."            ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Aavas Financiers Ltd."                   "7.21"       "2394.85"    "2382.35"    "2309.55"    "2259.2"     "2228.95"    "2162.85"    "2210.3"     "17976.62"  
"Aayush Food & Herbs Ltd."                "1.81"       "22.65"      "21.95"      "21.95"      "21.6"       "22"         "22"         "21.3"       "7.17"      
"Aban Offshore Ltd."                      ""           "45.3"       "45.55"      "45.6"       "45.2"       "44.65"      "46"         "45.3"       "337.64"    
"Abans Enterprises Ltd."                  "7.79"       "115.05"     "119.75"     "113.05"     "113.45"     "111.2"      "109.4"      "107.2"      "143.68"    
"Abate A S Inds. Ltd."                    "16.83"      "8.63"       "8.2"        "7.79"       "8.17"       "7.77"       "8.15"       "8.55"       "6.62"      
"Abbott India Ltd."                       "13.34"      "20541.95"   "20531.8"    "20629.55"   "20420.95"   "20395.55"   "20292.2"    "19608.2"    "39847.86"  
"Abhayam Trading Ltd."                    ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Abhijit Trading Co. Ltd."                ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Abhinav Capital Services Ltd."           "0.74"       "94.5"       "97.8"       "93.4"       "92.45"      "87.85"      "89.8"       "85.9"       "71.32"    
"Abhinav Leasing & Finance Ltd."          "2.42"       "1.95"       "2"          "1.95"       "1.92"       "1.95"       "1.92"       "1.95"       "9.7"      
"Abhishek Corporation Ltd."               ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Abhishek Finlease Ltd."                  "4.45"       ""           ""           "39.85"      ""           "39"         ""           "38"         "13.05"    
"Abhishek Infraventures Ltd."             "0.55"       "3.88"       ""           "3.88"       "3.88"       "3.88"       "3.69"       "3.69"       "1.35"      
"Abirami Financial Services (India) Ltd." "0.41"       "6.47"       ""           ""           ""           ""           "6.79"       ""           "4.07"      
"Accedere Ltd."                           "4.69"       "30.65"      "29.25"      "30"         "31"         "31.95"      "30.4"       "28.9"       "14.33"    
"Accel Ltd."                              "3.21"       "18.45"      "18.4"       "18"         "18.25"      "18.1"       "18"         "18.05"      "100.31"    
"Accelya Solutions India Ltd."            "6.05"       "958.7"      "979.85"     "1061.6"     "1084.05"    "1088.2"     "1066.7"     "1050.6"     "1681.67"  
"Ace Engitech Ltd."                       "5.17"       "15.06"      "14.32"      "14.33"      "13.63"      "13.63"      "13.5"       "14.17"      "4.86"      
"Ace Men Engg. Works Ltd."                "3.56"       "61.8"       "63"         "63.75"      "62"         "58.9"       "57.2"       "58.45"      "16.03"    
"Ace Software Exports Ltd."               "0.51"       "16.25"      "15.55"      "16.45"      "14.85"      ""           "14.85"      "15.55"      "8.49"      
"Acewin Agriteck Ltd."                    "0.18"       "3.63"       ""           ""           ""           ""           "3.45"       ""           "3.36"      
"Achal Investments Ltd."                  ""           ""           ""           ""           ""           ""           ""           ""           ""          
"Achyut Healthcare Ltd."                  "1.53"       ""           ""           ""           ""           ""           "18"         ""           "13.14"    
end

rename v1 company_name
ds company_name, not
local headings `r(varlist)'
local newnames
foreach h of local headings {
    local date = (daily(`h'[1], "DMY"))
    local prefix = `h'[2]
    display `"`prefix'"', `date'
    local newnames `newnames' `prefix'`date'
}
rename (`headings') (`newnames')
drop in 1/2
reshape long pbratio pr mcap, i(company_name) j(date)
format date %td
destring mcap pbratio pr, replace
format mcap pbratio pr %3.2f

gen year = yofd(date)

//  CREATE MCAP GROUPS
by year, sort: egen mcap_half = xtile(mcap), nq(2)
by year company_name (mcap_half), sort: replace mcap_half = mcap_half[1]

//  CREATE PBRATIO GROUPS
by year: egen pbratio_tertile = xtile(pbratio), nq(3)
by year company_name (pbratio_tertile), sort: replace pbratio_tertile = pbratio_tertile[1]

//  CREATE MCAP # PBRATIO GROUPS
egen pbratio_mcap_group = group(mcap_half pbratio_tertile), label

[Sartaj Hussain]

Thanks indeed. Last time we had worked on a code which creates the six intersection groups. I will share that again soon for any improvements.

[Matthew Williams]

Dear Prof. Clyde,

May I ask the logic behind command by ... , sort in #10?
For a simple sort, I can understand. For example

by year, sort: egen mcap_half = xtile(mcap), nq(2)

means that data will be sorted by year. However, for a more complicated sort, I don't fully understand. For example

by year company_name (mcap_half), sort: replace mcap_half = mcap_half[1]

Now, we have three variables year company_name (mcap_half), so could you please explain the logic behind this command to me?

Thank you.

[Clyde Schechter]

The data are first sorted in order by year, company-name within year, and mcap_half within combinations of year and company name. One thing to understand at this juncture is that the variable mcap_half is a variable that takes on either the value 1 or 2 in a single observation for each such combination, and missing in the others. Since missing value sorts after all non-missing values for numeric variables in Stata, this means that the first observation within each year company_name group will be the one that has the non-missing value. The other thing to understand is the use of parentheses in -by varlist1 (varlist2)- commands. Both varlists are used to determine the sort order (as already explained). But only the first, unparenthesized varlist determines the groups over which the command is iterated.

So, the effect of this command is that the data are sorted on year, company_name within that, and mcap_half within those, and then all of the observations in each year company_name combination have their value of mcap_half replaced by the value in the first observation in the group (which, in the context of the code that preceded this command is the only one with a non-missing value). Put more concisely, the one and only non-missing value of mcap_half within each group defined by year X company_name combination is spread to all the observations in the group.

[Matthew Williams]

Dear Prof. Clyde,

Thank you so much for the detailed and easy-to-understand explanations. Things are now clear to me.

[Sartaj Hussain]

I append below actual code as mentioned in #11 of this thread. The data example that we used for this code previously is also given. In the appended code, we used same year march month bmr values. Now i want to run this code and bmr values will be of immediately preceding year December month. For example, this example data set runs from June of year t to June of year t+1 and has mcap and bmr values respectively for each stock for June and March month of year t. Now, in the actual data, the mcap values for each stock will continue to be for June of year t, but bmr values will for December month of year t-1. What is the change to be introduced for so that sorts for mcap take latest june mcap values and sorts for bmr use preceding bmr values of December.

* Example generated by -dataex-. For more info, type help dataex
clear
input int stock_id str52 stock float(date mdate pr mcap bmr)
1 "3M India Ltd." 15430 506      .      . .1984127
1 "3M India Ltd." 15491 508  282.7      .        .
1 "3M India Ltd." 15494 509 275.25      .        .
1 "3M India Ltd." 15495 509  288.5      .        .
1 "3M India Ltd." 15496 509  282.6      .        .
1 "3M India Ltd." 15497 509  284.9      .        .
1 "3M India Ltd." 15498 509 276.05      .        .
1 "3M India Ltd." 15501 509      .      .        .
1 "3M India Ltd." 15502 509  287.9      .        .
1 "3M India Ltd." 15503 509    285      .        .
1 "3M India Ltd." 15504 509  278.7      .        .
1 "3M India Ltd." 15505 509    283      .        .
1 "3M India Ltd." 15508 509  287.9      .        .
1 "3M India Ltd." 15509 509 294.95      .        .
1 "3M India Ltd." 15510 509  294.5      .        .
1 "3M India Ltd." 15511 509    294      .        .
1 "3M India Ltd." 15512 509    284      .        .
1 "3M India Ltd." 15515 509 290.05      .        .
1 "3M India Ltd." 15516 509  289.1      .        .
1 "3M India Ltd." 15517 509  282.5      .        .
1 "3M India Ltd." 15518 509    290      .        .
1 "3M India Ltd." 15519 509 285.15 321.22        .
1 "3M India Ltd." 15522 510  287.2      .        .
1 "3M India Ltd." 15523 510 285.05      .        .
1 "3M India Ltd." 15524 510    295      .        .
1 "3M India Ltd." 15525 510  324.5      .        .
1 "3M India Ltd." 15526 510  318.3      .        .
1 "3M India Ltd." 15529 510    318      .        .
1 "3M India Ltd." 15530 510  323.6      .        .
1 "3M India Ltd." 15531 510  313.4      .        .
1 "3M India Ltd." 15532 510    302      .        .
1 "3M India Ltd." 15533 510  301.7      .        .
1 "3M India Ltd." 15536 510    295      .        .
1 "3M India Ltd." 15537 510    300      .        .
1 "3M India Ltd." 15538 510    292      .        .
1 "3M India Ltd." 15539 510 295.85      .        .
1 "3M India Ltd." 15540 510  303.5      .        .
1 "3M India Ltd." 15543 510    291      .        .
1 "3M India Ltd." 15544 510 302.75      .        .
1 "3M India Ltd." 15545 510 299.45      .        .
1 "3M India Ltd." 15546 510    301      .        .
1 "3M India Ltd." 15547 510    300      .        .
1 "3M India Ltd." 15550 510  301.7      .        .
1 "3M India Ltd." 15551 510    300      .        .
1 "3M India Ltd." 15552 510    300      .        .
1 "3M India Ltd." 15553 511    300      .        .
1 "3M India Ltd." 15554 511  302.9      .        .
1 "3M India Ltd." 15557 511 303.75      .        .
1 "3M India Ltd." 15558 511    304      .        .
1 "3M India Ltd." 15559 511  306.8      .        .
1 "3M India Ltd." 15560 511    300      .        .
1 "3M India Ltd." 15561 511 300.75      .        .
1 "3M India Ltd." 15564 511  300.4      .        .
1 "3M India Ltd." 15565 511    300      .        .
1 "3M India Ltd." 15566 511    300      .        .
1 "3M India Ltd." 15568 511  304.1      .        .
1 "3M India Ltd." 15571 511    300      .        .
1 "3M India Ltd." 15572 511    303      .        .
1 "3M India Ltd." 15573 511 301.95      .        .
1 "3M India Ltd." 15574 511  302.1      .        .
1 "3M India Ltd." 15575 511  303.4      .        .
1 "3M India Ltd." 15578 511      .      .        .
1 "3M India Ltd." 15579 511    305      .        .
1 "3M India Ltd." 15580 511 305.25      .        .
1 "3M India Ltd." 15581 511    305      .        .
1 "3M India Ltd." 15582 511    316      .        .
1 "3M India Ltd." 15585 512    315      .        .
1 "3M India Ltd." 15586 512  313.5      .        .
1 "3M India Ltd." 15587 512    315      .        .
1 "3M India Ltd." 15588 512    315      .        .
1 "3M India Ltd." 15589 512  310.4      .        .
1 "3M India Ltd." 15592 512  306.5      .        .
1 "3M India Ltd." 15594 512    310      .        .
1 "3M India Ltd." 15595 512      .      .        .
1 "3M India Ltd." 15596 512    310      .        .
1 "3M India Ltd." 15599 512    310      .        .
1 "3M India Ltd." 15600 512 328.35      .        .
1 "3M India Ltd." 15601 512    325      .        .
1 "3M India Ltd." 15602 512 320.05      .        .
1 "3M India Ltd." 15603 512  316.9      .        .
1 "3M India Ltd." 15606 512 315.35      .        .
1 "3M India Ltd." 15607 512  312.6      .        .
1 "3M India Ltd." 15608 512  314.4      .        .
1 "3M India Ltd." 15609 512 316.75      .        .
1 "3M India Ltd." 15610 512  315.1      .        .
1 "3M India Ltd." 15613 512    315      .        .
1 "3M India Ltd." 15614 513    311      .        .
1 "3M India Ltd." 15616 513    315      .        .
1 "3M India Ltd." 15617 513    312      .        .
1 "3M India Ltd." 15620 513      .      .        .
1 "3M India Ltd." 15621 513  311.8      .        .
1 "3M India Ltd." 15622 513  315.1      .        .
1 "3M India Ltd." 15623 513    315      .        .
1 "3M India Ltd." 15624 513    315      .        .
1 "3M India Ltd." 15627 513    315      .        .
1 "3M India Ltd." 15629 513    315      .        .
1 "3M India Ltd." 15630 513    315      .        .
1 "3M India Ltd." 15631 513    315      .        .
1 "3M India Ltd." 15634 513    314      .        .
1 "3M India Ltd." 15635 513    313      .        .
end
format %td date
format %tm mdate

bysort stock_id (date):gen rt =((pr[_n]-pr[_n-1])/pr[_n-1])
gen moy = month(dofm(mdate))
gen year = year(dofm(mdate))
//  CREATE A "FISCAL YEAR" RUNNING FROM JULY THROUGH SUBSEQUENT JUNE
gen fyear = cond(moy > 6, year, year-1)
frame put stock_id fyear mcap bmr, into(mcap_bmr_work)
frame change mcap_bmr_work
collapse (count) n_mcap = mcap n_bmr = bmr (firstnm) mcap bmr, by(stock_id fyear)
assert n_mcap <= 1 & n_bmr <= 1 // VERIFY UNIQUE VALUE OF MCAP AND BMR
replace fyear = fyear + 1 // CHANGE THE FYEAR TO WHICH THEY WILL APPLY
frame change default
rename (mcap bmr) orig=
frlink m:1 stock_id fyear, frame(mcap_bmr_work)
frget mcap bmr, from(mcap_bmr_work)
frame drop mcap_bmr_work
drop mcap_bmr_work
egen byte representative = tag(stock_id fyear)

//  MEDIAN SPLIT BASED ON JUNE VALUE OF mcap
capture program drop one_year_median_split
program define one_year_median_split
    xtile june_mcap_group = mcap, nq(2)
    exit
end
frame put stock_id fyear mcap if representative & !missing(mcap), into(median_split) // ***
frame change median_split
runby one_year_median_split, by(fyear)
frame change default
frlink m:1 stock_id fyear, frame(median_split stock_id fyear) // ***
frget june_mcap_group, from(median_split)
frame drop median_split
drop median_split

//  NOW SPLIT AT 30TH AND 70TH PERCENTILES OF bmr
capture program drop one_year_three_groups
program define one_year_three_groups
    if _N > = 3 {
        _pctile bmr, percentiles(30 70)
        gen cut = `r(r1)' in 1
        replace cut = `r(r2)' in 2
        xtile march_bmr_group = bmr, cutpoints(cut)
    }
    else {
        gen march_bmr_group = .
    }
    exit
end
frame put stock_id fyear bmr if representative & !missing(bmr), into(three_groups) // ***
frame change three_groups
runby one_year_three_groups, by(fyear) verbose
frame change default
frlink m:1 stock_id fyear, frame(three_groups stock_id fyear) // ***
frget march_bmr_group, from(three_groups)
frame drop three_groups
drop three_groups

capture program drop one_weighted_return
program define one_weighted_return
    if !missing(june_mcap_group, march_bmr_group) {
        egen numerator = total(mcap*rt)
        egen denominator = total(mcap)
        gen vw_mean_rt = numerator/denominator
    }
    exit
end
drop if missing(june_mcap_group, march_bmr_group)
runby one_weighted_return, by(date june_mcap_group march_bmr_group)

collapse (first) vw_mean_rt, by(date june_mcap_group march_bmr_group)
drop if missing(vw_mean_rt)
keep date june_mcap_group march_bmr_group vw_mean_rt

isid june_mcap_group march_bmr_group date, sort
by date june_mcap_group, sort: egen temp = mean(vw_mean_rt)
by date (june_mcap_group), sort: gen SMB = temp[1] - temp[_N]
drop temp

by date march_bmr_group, sort: egen temp = mean(vw_mean_rt)
by date (march_bmr_group): gen HML = temp[1] - temp[_N]
drop temp

//  AND IF YOU WANT TO REDUCE TO ONE OBSERVATION PER MONTH
label define june_mcap_group 1 "S" 2 "B"
label define march_bmr_group 1 "L" 2 "M" 3 "H"
label values june_mcap_group june_mcap_group
label values march_bmr_group march_bmr_group
decode june_mcap_group, gen (mcap_group)
decode march_bmr_group, gen(bmr_group)
drop june_mcap_group march_bmr_group
egen groups = concat(mcap_group bmr_group)
keep date groups SMB HML vw_mean_rt
rename vw_mean_rt =_
reshape wide vw_mean_rt_, i(date) j(groups) string