Merge when the only ID variable in datasets is a string variable

[Sunita Rao]

The only ID variable/key variable in my datasets is a string variable called CIQ_ID. Each CIQ_ID is a company.

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 CIQ_ID
"IQ18511"    
"IQ289194"   
"IQ997111"   
"IQ875418"   
"IQ526391"   
"IQ142742"   
"IQ248270"   
"IQ312375"   
"IQ18527"    
"IQ247483"   
"IQ141885706"
"IQ247589"   
"IQ109019149"
"IQ5463655"  
"IQ972190"   
"IQ648902"   
"IQ780678"   
"IQ4222231"  
"IQ389218"   
"IQ92816"    
"IQ728553"   
"IQ24321"    
"IQ24333"    
"IQ168864"   
"IQ875505"   
"IQ128534719"
"IQ247906"   
"IQ136994486"
"IQ874662"   
"IQ250178"   
"IQ327168517"
"IQ873693"   
"IQ876955"   
"IQ154924"   
"IQ248095"   
"IQ98900561"
"IQ248356"   
"IQ561001"   
"IQ874805"   
"IQ873739"   
"IQ24522"    
"IQ325843"   
"IQ248501"   
"IQ18671"    
"IQ694234"   
"IQ365138"   
"IQ419896"   
"IQ882318"   
"IQ42083601"
"IQ24568"    
"IQ876162"   
"IQ241782379"
"IQ312949"   
"IQ598807"   
"IQ29096"    
"IQ387473"   
"IQ296308"   
"IQ883733"   
"IQ18749"    
"IQ134386019"
"IQ878918"   
"IQ401715"   
"IQ373264"   
"IQ813607"   
"IQ168569"   
"IQ135470"   
"IQ92001"    
"IQ250388"   
"IQ24766"    
"IQ250885"   
"IQ4165019"  
"IQ24809"    
"IQ31860"    
"IQ24816"    
"IQ24827"    
"IQ877088"   
"IQ874001"   
"IQ251411"   
"IQ409115"   
"IQ745257"   
"IQ24893"    
"IQ35196211"
"IQ160077"   
"IQ119853"   
"IQ169838"   
"IQ3799924"  
"IQ24937"    
"IQ251230"   
"IQ59502420"
"IQ364040"   
"IQ251704"   
"IQ62120637"
"IQ33348547"
"IQ662734"   
"IQ11969433"
"IQ58830719"
"IQ252186"   
"IQ877335"   
"IQ874120"   
"IQ642957"   
end

Further, CIQ_ID in my master file is listed in a column. However, in all of my using datasets, each CIQ_ID has about 520 observations (OpenPrice, ClosePrice & AdjustedClosePrice for each company, i.e. each CIQ_ID for two years; different files for each of OpenPrice, ClosePrice & AdjustedClosePrice) associated with it, and, hence is listed in a row.

Question 1: How can I convert the string variable CIQ_ID to a numeric one, so that I can go ahead with the merge?

Question 2: Is the column listing of CIQ_ID in the master file and the row listing of CIQ_ID in the using file going to pose a problem for the merge? How can I overcome that problem?

[Jared Greathouse]

Show both datasets please.

[William Lisowski]

Question 1: How can I convert the string variable CIQ_ID to a numeric one, so that I can go ahead with the merge?

Merge works with string variables as well as numeric variables, so there is no need to convert it to a numeric variable.

Question 2: Is the column listing of CIQ_ID in the master file and the row listing of CIQ_ID in the using file going to pose a problem for the merge? How can I overcome that problem?

Descriptions of data are well-meant but insufficient to help those who want to help you. Even the best descriptions of data are no substitute for an actual example of the data. There are many ways your data might be organized that are consistent with your description, and each would require a somewhat different approach. In order to get a helpful response, you need to show some example data.

[Sunita Rao]

I understand and I will be careful next time.

Here are samples of all datasets:

Here is a sample from the master file:

* Example generated by -dataex-. For more info, type help dataex
clear
input str12 CIQ_ID str16 HdqtrsCountry double(LTDebtFY2018 TotalAssetsFY2018) str8 Exchange double(ROAFY2018 TotalSalesFY2018) str72 AuditorFY2018 str36 AuditOpFY2018 byte(ESGScrFY2018 ENVScrFY2018 SocialScrFY2018 GOVScrFY2018) double TotalDebtFY2020
"IQ18511"     "United Kingdom"   807.07418   11087.09383 "LSE"       12.3785   103.86694 "Ernst & Young LLP"                            "Unqualified"                          45 46 37 50    740.77763
"IQ289194"    "USA"                  13428         36500 "NYSE"      13.0341       32765 "PricewaterhouseCoopers LLP"                   "Unqualified"                          68 66 71 68        19775
"IQ997111"    "USA"                  221.4        3071.5 "NYSE"      10.9728      3187.9 "Ernst & Young LLP"                            "Unqualified"                          11  1  4 27        158.7
"IQ875418"    "Denmark"               8036         56622 "CPSE"         .631       39280 "PricewaterhouseCoopers LLP"                   "Unqualified"                          26 31 24 24        15373
"IQ526391"    "Sweden"           697.43419   10950.09936 "OM"         3.1434 12992.55305 "Deloitte & Touche LLP"                        "Unqualified"                          69 88 66 62   2225.81556
"IQ142742"    "Sweden"           231.69038   10491.89478 "OM"        -5.1543           0 "PricewaterhouseCoopers LLP"                   "Unqualified"                          23 12 21 29    946.82395
"IQ248270"    "Sweden"          1671.00641    9809.20145 "OM"         7.0808  9644.91395 "PricewaterhouseCoopers LLP"                   "Unqualified"                          36 45 41 28   2234.21716
"IQ312375"    "Sweden"          9749.67536   53411.77874 "OM"         4.5485 42508.15249 "Deloitte & Touche LLP"                        "Unqualified"                          27 25 27 29   18767.5946
"IQ18527"     "Switzerland"           6458         44441 "SWX"        3.2523       27662 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          41 47 40 37         7124
"IQ247483"    "USA"                  19359         67173 "NYSE"        3.345       30578 "Ernst & Young LLP"                            "Unqualified"                          82 87 80 81        19890
"IQ141885706" "USA"                  35002         59352 "NYSE"      11.4522       32753 "Ernst & Young LLP"                            "Unqualified with Additional Language" 80 92 85 73        87097
"IQ247589"    "USA"                      0       786.375 "NASDAQGS"  14.8185     593.749 "Deloitte & Touche LLP"                        "Unqualified with Additional Language" 16  0  1 29        12.22
"IQ109019149" "Netherlands"    72591.02358   436564.0027 "ENXTAM"      .6071  2862.37692 "Ernst & Young LLP"                            "Unqualified"                          83 93 83 79 115969.41894
"IQ5463655"   "United Kingdom"  1377.75455    15953.1487 "LSE"         .3056  2491.68377 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          71 74 69 70   1486.81121
"IQ972190"    "Ireland"             19.676     24449.083 "NYSE"      15.6419   40992.534 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          53 65 41 54     3485.513
"IQ648902"    "France"           1934.9668    14855.7362 "ENXTPA"     2.5244  3757.72842 "Deloitte & Associes, Ernst & Young Et Autres" "Unqualified with Additional Language" 66 67 65 66   4719.26605
"IQ780678"    "Spain"           7122.71239    40876.1438 "BME"        3.0636 41953.35356 "Deloitte & Touche LLP"                        "Unqualified"                          68 77 61 67  14582.08318
"IQ4222231"   "USA"                   2671         17890 "NASDAQGS"   6.8316        7500 "PricewaterhouseCoopers LLP"                   "Unqualified with Additional Language" 12  0  3 21         3895
"IQ389218"    "Switzerland"     1739.18022   11126.63155 "SWX"         5.986 27326.53996 "Ernst & Young LLP"                            "Unqualified"                          28 44 20 30    2829.3578
"IQ92816"     "Germany"         1842.22578   17874.97137 "XTRA"      10.1582 25091.59606 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          84 98 85 75   7204.89297
"IQ728553"    "United Kingdom"   391.65955    8669.91244 "LSE"        4.8483   921.22201 "Deloitte & Touche LLP"                        "Unqualified"                          20 16 12 28    834.74414
"IQ24321"     "USA"               4134.544     18768.682 "NASDAQGS"  10.6593        9030 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          65 71 61 64         4708
"IQ24333"     "USA"                1045.72      9040.648 "NYSE"       4.4061    9580.554 "Deloitte & Touche LLP"                        "Unqualified"                          10  3  4 17     3510.071
"IQ168864"    "USA"                   1114          4556 "NASDAQGS"    6.953        6475 "Ernst & Young LLP"                            "Unqualified with Additional Language" 23 29 16 23          531
"IQ875505"    "Japan"                    0    2396.86467 "TSE"        6.2155  1951.16058 "Ernst & Young ShinNihon"                      "Unqualified"                          43 69 31 28    104.90512
"IQ128534719" "Netherlands"              0    2130.08473 "ENXTAM"     7.2169  1892.54408 "PricewaterhouseCoopers LLP"                   "Unqualified"                           3  0  0  6    160.83792
"IQ247906"    "Netherlands"    15681.13599  452560.60942 "ENXTAM"      .1738 27347.03301 "PricewaterhouseCoopers LLP"                   "Unqualified"                          59 55 48 68  17233.02752
"IQ136994486" "Spain"           7582.55896   17058.70162 "BME"         7.521  4816.10602 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          25 35 19 22  10280.15902
"IQ874662"    "Japan"          14419.02198   88575.30134 "TSE"        1.4488 69158.23767 "Deloitte Touche Tohmatsu CPA Ltd"             "Unqualified"                          64 78 51 64  28085.10571
"IQ250178"    "USA"                   5765        140406 "NYSE"       1.8933       22119 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          37 24 25 52         8863
"IQ327168517" "Australia"        119.20266     290.10725 ""            .0367    84.24482 "Ernst & Young LLP"                            "Unqualified"                           0  0  0  0    323.83665
"IQ873693"    "Japan"            3579.5379   20378.03401 "TSE"        3.3609 13880.54506 "KPMG AZSA LLC"                                "Unqualified"                          39 42 38 38   8330.97539
"IQ876955"    "Belgium"         3651.93497  116425.80718 "ENXTBR"      .8473 12209.64048 "PwC Bedrijfsrevisoren BCVBA"                  "Unqualified with Additional Language" 11  0  5 22   8210.39755
"IQ154924"    "USA"                   1799          8541 "NYSE"       7.1535        4914 "PricewaterhouseCoopers LLP"                   "Unqualified"                          47 63 49 44         2537
"IQ248095"    "Canada"            1721.308      7852.843 "NYSE"        .8121    2191.221 "Ernst & Young LLP"                            "Unqualified"                          31 29 16 49     1685.516
"IQ98900561"  "Hong Kong"             4496        229806 "SEHK"            0 36525.23381 "PricewaterhouseCoopers LLP"                   "Unqualified"                          50 50 50 50        11033
"IQ248356"    "USA"                 3352.8       19178.3 "NYSE"       6.3925      8930.2 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          66 74 56 66       8316.4
"IQ561001"    "Netherlands"     8193.26769  131896.03846 "ENXTPA"     1.8373 72941.37851 "Ernst & Young LLP"                            "Unqualified"                          31 48 22 27  20911.31498
"IQ874805"    "Japan"           4054.88461   33217.92917 "TSE"         4.653 36805.58529 "PricewaterhouseCoopers Aarata"                "Unqualified"                          29 46 22 17   9344.83605
"IQ873739"    "Japan"           2916.16228   13425.53616 "TSE"        4.1937 10830.08384 "Ernst & Young ShinNihon"                      "Unqualified"                          75 82 61 80   3316.98311
"IQ24522"     "USA"                 874.08       5461.77 "NASDAQGS"    5.138    2714.474 "PricewaterhouseCoopers LLP"                   "Unqualified with Additional Language" 49 53 40 53     2776.912
"IQ325843"    "Netherlands"     2023.12801   21506.75521 "ENXTAM"     2.1988  10597.6643 "PricewaterhouseCoopers LLP"                   "Unqualified"                          83 86 80 82    3535.1682
"IQ248501"    "USA"                   1621         10912 "NYSE"       4.5768        8264 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified with Additional Language" 57 45 54 66         5078
"IQ18671"     "USA"               1393.421      7581.674 "NYSE"       6.1553     3374.95 "PricewaterhouseCoopers LLP"                   "Unqualified with Additional Language" 23 22 22 25      3711.12
"IQ694234"    "Switzerland"              0         27062 "SWX"         .2777        7153 "PricewaterhouseCoopers LLP"                   "Unqualified"                           0  0  0  0         4503
"IQ365138"    "USA"               5479.023     14464.956 "NYSE"       1.8151    1314.781 "Ernst & Young LLP"                            "Unqualified"                          43 54 36 35     7909.036
"IQ419896"    "Sweden"           960.96934    6534.59152 "OM"         6.3708  4575.96947 "Ernst & Young LLP"                            "Unqualified"                          22 27 17 21   1416.70481
"IQ882318"    "Canada"            3323.747      9398.589 "TSX"        2.6229    1626.445 "Ernst & Young LLP"                            "Unqualified"                          11 12  0 20     4596.949
"IQ42083601"  "China"          19052.97048  114313.67831 "NYSE"       7.2112 39893.83568 "PricewaterhouseCoopers LLP"                   "Unqualified"                          11  0  7 17  20797.37363
"IQ24568"     "USA"                      0      2052.458 "NASDAQGS"  15.2016    1966.492 "PricewaterhouseCoopers LLP"                   "Unqualified with Additional Language" 14  0  0 26        86.18
"IQ876162"    "Canada"              8663.5       23156.7 "TSX"        7.0556     51394.4 "PricewaterhouseCoopers LLP"                   "Unqualified with Additional Language" 10  0  9 20      10616.7
"IQ241782379" "Ireland"             1409.5        2810.2 "NYSE"      12.6933      2731.7 "PricewaterhouseCoopers LLP"                   "Unqualified"                          12  5  3 27       1523.5
"IQ312949"    "USA"                 5246.3         15426 "NASDAQGS"   2.8576        3534 "Deloitte & Touche LLP"                        "Unqualified with Additional Language" 19 16 12 30         7181
"IQ598807"    "Germany"        33148.61461 1027670.02508 "XTRA"        .7378 99626.74604 "PricewaterhouseCoopers LLP"                   "Unqualified"                          85 85 86 85  49770.03057
"IQ29096"     "USA"                   3950        232792 "NASDAQGS"   9.4733      136819 "Ernst & Young LLP"                            "Unqualified with Additional Language" 31 61 22 24        27872
"IQ387473"    "France"          1172.84711     16318.837 "ENXTPA"     1.7386  9050.14168 "PricewaterhouseCoopers, Mazars"               "Unqualified with Additional Language" 81 82 81 80         1800
"IQ296308"    "USA"                  11898         55459 "NYSE"      12.4593       19627 "PricewaterhouseCoopers LLP"                   "Unqualified"                          63 88 49 60        29471
"IQ883733"    "Spain"            2990.7259   11598.46577 "BME"        9.8602   5651.1335 "Deloitte & Touche LLP"                        "Unqualified"                          76 86 74 72   6927.95107
"IQ18749"     "USA"                  23495        162648 "NASDAQGS"   5.2817      232887 "Ernst & Young LLP"                            "Unqualified"                          14 10  5 21       104740
"IQ134386019" "Brazil"           219.74918   24724.10228 "BOVESPA"   11.7519 12975.31477 "0"                                            "0"                                    20 21 23 18    922.78903
"IQ878918"    "Denmark"          186.97035     659.69371 "CPSE"      10.4692   406.03727 "Ernst & Young LLP"                            "Unqualified"                          14  7  9 19    228.88927
"IQ401715"    "Switzerland"         2685.2        9057.5 "ASX"        7.1861      9319.1 "PricewaterhouseCoopers LLP"                   "Unqualified"                          60 65 51 65         6784
"IQ373264"    "USA"                   7859         27215 "NYSE"       3.3554        6009 "PricewaterhouseCoopers LLP"                   "Unqualified"                          23 21 14 31        11576
"IQ813607"    "Mexico"         27614.55629   72732.16367 "BMV"        5.5846 52833.65176 "Mancera, S.C."                                "Unqualified"                          26 34 27 23  37073.84505
"IQ168569"    "USA"                  20566         60580 "NASDAQGS"   4.1425       41689 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified with Additional Language" 28 34 15 34        41021
"IQ135470"    "USA"                21648.2       68802.8 "NASDAQGS"   2.6939     15927.1 "PricewaterhouseCoopers LLP"                   "Unqualified"                          35 38 28 37      34720.6
"IQ92001"     "USA"                  58404        188602 "NYSE"       3.7431       31315 "PricewaterhouseCoopers LLP"                   "Unqualified"                          40 65 22 41        45418
"IQ250388"    "USA"                  34540        491984 "NYSE"        .2689       46645 "PricewaterhouseCoopers LLP"                   "Unqualified"                          27 27 13 38        38534
"IQ24766"     "USA"                18266.3       33010.4 "NYSE"       5.1761      7314.7 "Deloitte & Touche LLP"                        "Unqualified"                           9  1  5 25      36711.8
"IQ250885"    "USA"                   7575         21223 "NYSE"       3.5591        3440 "PricewaterhouseCoopers LLP"                   "Unqualified"                          25 30 20 24        11025
"IQ4165019"   "USA"                   2842        137216 "NYSE"       1.5415       11120 "PricewaterhouseCoopers LLP"                   "Unqualified"                          22 21  9 31         4019
"IQ24809"     "USA"               4510.828     37669.838 "NYSE"       2.8275  167939.635 "Ernst & Young LLP"                            "Unqualified"                          26 24 28 24     4597.614
"IQ31860"     "USA"               2273.837      8662.288 "NYSE"       8.0667    4845.872 "Ernst & Young LLP"                            "Unqualified"                          13  0  6 25     2586.846
"IQ24816"     "USA"                  29510         66416 "NASDAQGS"   9.0105       22533 "Ernst & Young LLP"                            "Unqualified"                          57 68 56 55        33445
"IQ24827"     "USA"                 2806.4       10044.9 "NYSE"      10.5243        8202 "Deloitte & Touche LLP"                        "Unqualified"                          12  4  1 25       4095.6
"IQ877088"    "Australia"         571.5492     4741.7698 "ASX"        7.8557 15316.70566 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          22 13 17 36   1323.63833
"IQ874001"    "Japan"           6432.70121   24127.50926 "TSE"        4.2167 18565.97235 "Deloitte Touche Tohmatsu CPA Ltd"             "Unqualified"                          78 89 74 74   7833.14474
"IQ251411"    "USA"               6265.674     20438.366 "NASDAQGS"   5.8918    6224.689 "Ernst & Young LLP"                            "Unqualified"                          29 37 18 29     5688.103
"IQ409115"    "United Kingdom"        8422         52196 "LSE"        6.0722       27610 "Deloitte & Touche LLP"                        "Unqualified"                          63 65 64 62        13537
"IQ745257"    "Belgium"             105422        233868 "ENXTBR"     4.4094       53041 "Deloitte & Touche LLP"                        "Unqualified"                          30 41 19 31        98564
"IQ24893"     "USA"                      0      3265.964 "NASDAQGS"   9.6676    1293.636 "Deloitte & Touche LLP"                        "Unqualified with Additional Language" 13  3  8 20      919.058
"IQ35196211"  "China"             10.17782    3543.95031 "SEHK"      16.4039  3504.08407 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          27 44 20 24   2595.19652
"IQ160077"    "USA"                  17217         71571 "NYSE"       5.1174       86391 "Ernst & Young LLP"                            "Unqualified with Additional Language" 55 49 52 60        20992
"IQ119853"    "Ireland"               5993         26422 "NYSE"       4.2753       10770 "Ernst & Young LLP"                            "Unqualified with Additional Language" 16 10  7 25         8925
"IQ169838"    "USA"                   8054         21582 "NASDAQGS"    4.738        7502 "Ernst & Young LLP"                            "Unqualified"                          29 19 26 37         8888
"IQ3799924"   "Australia"       6984.35503   11262.74112 "ASX"        3.9036  1749.11095 "Deloitte Touche Tohmatsu CPA Ltd"             "Unqualified"                          19 20 19 18   7976.44366
"IQ24937"     "USA"                  93735        365725 "NASDAQGS"  11.9591      265595 "Ernst & Young LLP"                            "Unqualified"                          27 45  8 26       122278
"IQ251230"    "USA"                   5309         17633 "NASDAQGS"  15.1678       16705 "KPMG LLP - Klynveld Peat Marwick Goerdeler"   "Unqualified"                          29 39 18 28         5707
"IQ59502420"  "Ireland"               4038         12480 "NYSE"       8.1037       14435 "Ernst & Young LLP"                            "Unqualified"                          32 44  4 48         4501
"IQ364040"    "Luxembourg"            8961         91249 "ENXTAM"     4.9385       76033 "Deloitte & Touche LLP"                        "Unqualified"                          50 50 44 57        12322
"IQ251704"    "USA"                   7698         40833 "NYSE"       2.9766       64341 "Ernst & Young LLP"                            "Unqualified"                          63 74 61 58        11068
"IQ62120637"  "Netherlands"              0     662.30593 "ENXTBR"   -10.7767    24.59583 "Deloitte & Touche LLP"                        "Unqualified with Additional Language"  0  0  0  0        9.657
"IQ33348547"  "USA"                      0      3081.983 "NYSE"      15.2978    2151.369 "Ernst & Young LLP"                            "Unqualified"                           9  3  2 18        90.17
"IQ662734"    "Australia"       2084.83533    4232.21137 "ASX"       12.5304  2540.35469 "PricewaterhouseCoopers LLP"                   "Unqualified"                          13  0  8 21    2568.3871
end

Here is a sample from the using file called OpenPrice. Please note that CIQ_IDs/companies/Key variables are listed horizontally on the first row. The dates and the corresponding OpenPrice of each CIQ_ID/company is in columns.

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 A str17 B str9 C str17 D str9(E F G H) str17 I str9 J
"CIQ_ID"     "IQ18511"   "IQ289194"  "IQ997111"          "IQ875418"  "IQ526391"  "IQ142742"  "IQ248270"  "IQ312375"  "IQ18527"  
"Dates"      "OpenPrice" "OpenPrice" "OpenPrice"         "OpenPrice" "OpenPrice" "OpenPrice" "OpenPrice" "OpenPrice" "OpenPrice"
"12/31/2021" "14.49"     "177.53"    "85.13"             "23650"     "219.3"     "289.2"     "214.1"     "209.75"    "35.15"    
"12/30/2021" "14.66"     "178.87"    "85.90000000000001" "23410"     "218.4"     "289.8"     "213.1"     "209.25"    "35.15"    
"12/29/2021" "14.71"     "177.48"    "85.14"             "23350"     "218.7"     "288.4"     "211.8"     "208.95"    "35"       
"12/24/2021" "14.62"     "176.84"    "84.81"             "22800"     "214"       "284.8"     "209.4"     "207.45"    "34.52"    
"12/23/2021" "14.355"    "175.15"    "83.34"             "22530"     "211.9"     "279.6"     "205.8"     "204"       "34.11"    
"12/22/2021" "14.035"    "172.99"    "81.79000000000001" "22410"     "209.5"     "275"       "204.8"     "201.3"     "34"       
"12/21/2021" "14.185"    "172.92"    "80.94"             "22540"     "209.9"     "274.8"     "204.8"     "198"       "33.87"    
"12/20/2021" "13.995"    "173.44"    "80"                "21610"     "208.7"     "270.2"     "203.7"     "197.6"     "33.51"    
"12/17/2021" "14.11"     "173.21"    "80.31999999999999" "22010"     "210.2"     "276.8"     "206.5"     "198.64"    "34.51"    
"12/16/2021" "14.25"     "177.86"    "83.3"              "21780"     "213.1"     "282.4"     "207.4"     "200"       "34.1"     
"12/15/2021" "13.945"    "176.71"    "84.34999999999999" "21020"     "207.4"     "281.4"     "201.9"     "197.4"     "33.37"    
"12/14/2021" "14.04"     "175.08"    "82.27"             "21410"     "210.7"     "285.4"     "204"       "200.05"    "34.05"    
"12/13/2021" "13.93"     "174.85"    "83.29000000000001" "21380"     "213.5"     "285.2"     "203.6"     "201"       "33.79"    
"12/10/2021" "13.88"     "177"       "83.65000000000001" "21400"     "210.2"     "282.2"     "205.4"     "202"       "33.84"    
" 12/9/2021" "14.09"     "177.94"    "83.14"             "21200"     "212.5"     "282"       "210"       "203.35"    "33.99"    
" 12/8/2021" "14.585"    "176.49"    "83.05"             "20670"     "213.2"     "281.6"     "213"       "205"       "33.7"     
" 12/7/2021" "14.105"    "176.5"     "82.95"             "20140"     "213"       "276.2"     "213.5"     "202.6"     "33.54"    
" 12/6/2021" "14.195"    "177.11"    "82.97"             "20200"     "208.5"     "272.4"     "209.8"     "198.7"     "32.68"    
" 12/3/2021" "14.095"    "174.52"    "82.34"             "20030"     "211"       "275"       "215.7"     "201.7"     "32.44"    
" 12/2/2021" "13.94"     "169.9"     "81.56"             "20200"     "206.6"     "272.6"     "210.2"     "200.8"     "31.82"    
" 12/1/2021" "13.745"    "171.54"    "79.91"             "19935"     "203"       "271.4"     "209.8"     "197.96"    "32.09"    
"11/30/2021" "13.755"    "172.55"    "80.31999999999999" "19690"     "201.8"     "271.8"     "206.6"     "194.4"     "31.61"    
"11/29/2021" "13.85"     "174.7"     "80.44"             "19740"     "206.1"     "275.6"     "208"       "199.92"    "31.48"    
"11/26/2021" "14.08"     "176.47"    "81.84999999999999" "18890"     "204.8"     "279"       "212"       "201"       "31.67"    
end

Here is a sample from another using file called ClosePrice:

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 A str17 B str10 C str17 D str10(E F G H) str17 I str10 J
"CIQ_ID"     "IQ18511"    "IQ289194"   "IQ997111"          "IQ875418"   "IQ526391"   "IQ142742"   "IQ248270"   "IQ312375"   "IQ18527"   
"Dates"      "ClosePrice" "ClosePrice" "ClosePrice"        "ClosePrice" "ClosePrice" "ClosePrice" "ClosePrice" "ClosePrice" "ClosePrice"
"12/31/2021" "14.49"      "177.63"     "85.84999999999999" "23450"      "219.5"      "288.4"      "214.5"      "209.65"     "34.9"      
"12/30/2021" "14.58"      "177.64"     "85.29000000000001" "23650"      "219"        "288.8"      "214.1"      "209.75"     "35.18"     
"12/29/2021" "14.675"     "178.41"     "85.81"             "23410"      "218.4"      "290"        "213.6"      "209.55"     "35.15"     
"12/24/2021" "14.545"     "177.64"     "85.54000000000001" "23340"      "218.7"      "288.2"      "211.5"      "208.8"      "34.95"     
"12/23/2021" "14.445"     "176.7"      "84.7"              "22780"      "214.3"      "284.6"      "211"        "207.8"      "34.63"     
"12/22/2021" "14.295"     "174.97"     "82.77"             "22500"      "210.4"      "278.2"      "204.7"      "202.9"      "33.92"     
"12/21/2021" "14.055"     "172.64"     "81.59999999999999" "22310"      "208.9"      "274.2"      "204.8"      "200.6"      "33.94"     
"12/20/2021" "13.975"     "172.92"     "80.90000000000001" "22310"      "208.3"      "271"        "203.1"      "196.2"      "33.41"     
"12/17/2021" "14.24"      "172.64"     "79.27"             "21890"      "212.3"      "275.8"      "208.3"      "198.2"      "34.14"     
"12/16/2021" "14.185"     "174.75"     "80.91"             "22170"      "210.3"      "278"        "207.1"      "199.2"      "34.54"     
"12/15/2021" "14"         "178.31"     "83.78"             "21530"      "209.8"      "278"        "203.2"      "197"        "33.53"     
"12/14/2021" "13.895"     "175.78"     "83.67"             "21010"      "207.4"      "280.8"      "201.7"      "196.92"     "33.39"     
"12/13/2021" "13.96"      "174.72"     "81.98"             "21400"      "210"        "284.4"      "201.5"      "199.34"     "33.78"     
"12/10/2021" "13.925"     "174.58"     "83.54000000000001" "21200"      "212.9"      "284.4"      "202.9"      "200.1"      "33.73"     
" 12/9/2021" "13.995"     "177.1"      "83.63"             "21520"      "210.9"      "282.6"      "206.2"      "203.15"     "33.93"     
" 12/8/2021" "14.05"      "175.94"     "82.87"             "21180"      "212"        "280.6"      "209.2"      "203"        "33.8"      
" 12/7/2021" "14.54"      "176.04"     "82.98"             "20620"      "213.5"      "281.6"      "213.6"      "204.7"      "33.56"     
" 12/6/2021" "14"         "176.55"     "82.89"             "19920"      "211.9"      "273.6"      "211.2"      "200.8"      "33.24"     
" 12/3/2021" "14.085"     "177.2"      "82"                "20040"      "206.8"      "270.8"      "208.6"      "197.18"     "32.41"     
" 12/2/2021" "14"         "172.59"     "81.59999999999999" "19885"      "209.3"      "273.2"      "214.2"      "199.96"     "32.35"     
" 12/1/2021" "14.335"     "170.26"     "81.08"             "20060"      "207.9"      "275.4"      "213.7"      "202.6"      "32.28"     
"11/30/2021" "13.87"      "170.73"     "79.45999999999999" "19775"      "202.5"      "268.8"      "207.3"      "195.42"     "31.89"     
"11/29/2021" "13.88"      "170.04"     "79.05"             "19660"      "203.7"      "275"        "210.3"      "198.32"     "31.94"     
"11/26/2021" "13.625"     "176.23"     "80.86"             "19210"      "206.1"      "275.4"      "206"        "198.74"     "31.5"      
end

Here is another using file called Adjusted day close:

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 A str29(B C D)
"CIQ_ID"     "IQ18511"                       "IQ289194"                      "IQ997111"                     
"Dates"      "Dividend Adj. Day Close Price" "Dividend Adj. Day Close Price" "Dividend Adj. Day Close Price"
"12/31/2021" "14.13902"                      "174.16925"                     "85.15227"                     
"12/30/2021" "14.22684"                      "174.17906"                     "84.59681999999999"            
"12/29/2021" "14.31954"                      "174.93406"                     "85.1126"                      
"12/24/2021" "14.19269"                      "174.17906"                     "84.84479"                     
"12/23/2021" "14.09511"                      "173.25737"                     "84.01161999999999"            
"12/22/2021" "13.94874"                      "171.56108"                     "82.09730999999999"            
"12/21/2021" "13.71456"                      "169.27647"                     "80.93680999999999"            
"12/20/2021" "13.63649"                      "169.55102"                     "80.24250000000001"            
"12/17/2021" "13.89508"                      "169.27647"                     "78.62575"                     
"12/16/2021" "13.84141"                      "171.34536"                     "80.25242"                     
"12/15/2021" "13.66089"                      "174.836"                       "83.09910000000001"            
"12/14/2021" "13.55843"                      "172.3553"                      "82.98999000000001"            
"12/13/2021" "13.62186"                      "171.31595"                     "81.31373000000001"            
"12/10/2021" "13.58771"                      "171.17867"                     "82.86105000000001"            
" 12/9/2021" "13.65601"                      "173.64958"                     "82.95032"                     
" 12/8/2021" "13.70968"                      "172.51218"                     "82.19649"                     
" 12/7/2021" "14.18781"                      "172.61023"                     "82.3056"                      
" 12/6/2021" "13.66089"                      "173.11029"                     "82.21633"                     
" 12/3/2021" "13.74383"                      "173.74763"                     "81.33356000000001"            
" 12/2/2021" "13.66089"                      "169.22745"                     "80.93680999999999"            
" 12/1/2021" "13.79994"                      "166.94284"                     "80.42104"                     
"11/30/2021" "13.35229"                      "167.40368"                     "78.81421"                     
"11/29/2021" "13.36192"                      "166.72713"                     "78.40754"                     
"11/26/2021" "13.11644"                      "172.79653"                     "80.20283000000001"                 
end

All 3 using files need to be merged into the master file.

[William Lisowski]

Here is example code, using your CompanyPrice data, that shows how to accomplish several things.

• Renaming the variables from Excel column names to the CIQ_ID value given in the first row.
• Reshaping the data into a long layout with one observation for each combination of CIQ_ID and date
• Creating the appropriate Stata numeric variables from the string variables that were created by import delimited because you included the two rows of identification information within the data to be imported. (Had you specified the first row as variable names and the import to start at the third row, much of the code below would not have been needed.)

The resulting dataset now has CIQ_ID as a single variable, and your data item (CompanyPrice) as another single variable, and the dataset is ready to be merged with other datasets.

Rather than immediately merge this dataset with the master dataset, I would convert each of the remaining "using" datasets, and then merge them all together into a single using dataset with each of your variables. Then I would merge that one combined using dataset with your "master" dataset.

// see what the data looks like
describe
// note that all variables are strings 
list in 1/3, clean noobs
// fix the variable names
local dataitem = strtoname(B[2])
display "`dataitem'"
ds
local vars `r(varlist)'
foreach var of local vars {
    local newname = `var'[1]
    rename `var' `newname'
}
rename CIQ_ID date_str
drop in 1/2
// see what this looks like now
list in 1/3, clean noobs
// change it from wide to long
rename IQ* v_str=
reshape long v_str, i(date_str) j(IQ) string
// see what this looks like now
describe
// note that all variables are still strings 
list in 1/9, clean noobs
// change date from string to numeric Stata daily date
generate date = daily(date_str,"MDY"), after(date_str)
format date %td
// change value from string to number
destring v_str, generate(`dataitem')
// let's see what this looks like
list in 1/9, clean noobs abbreviate(32)

. // see what the data looks like
. describe

Contains data
 Observations:            26                  
    Variables:            10                  
--------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
--------------------------------------------------------------------------------
A               str11   %11s                  
B               str17   %17s                  
C               str9    %9s                   
D               str17   %17s                  
E               str9    %9s                   
F               str9    %9s                   
G               str9    %9s                   
H               str9    %9s                   
I               str17   %17s                  
J               str9    %9s                   
--------------------------------------------------------------------------------
Sorted by: 
     Note: Dataset has changed since last saved.

. // note that all variables are strings 
. list in 1/3, clean noobs

             A           B           C           D           E           F      
>      G           H           I           J  
        CIQ_ID     IQ18511    IQ289194    IQ997111    IQ875418    IQ526391    IQ
> 142742    IQ248270    IQ312375     IQ18527  
         Dates   OpenPrice   OpenPrice   OpenPrice   OpenPrice   OpenPrice   Ope
> nPrice   OpenPrice   OpenPrice   OpenPrice  
    12/31/2021       14.49      177.53       85.13       23650       219.3      
>  289.2       214.1      209.75       35.15  

. // fix the variable names
. local dataitem = strtoname(B[2])

. display "`dataitem'"
OpenPrice

. ds
A  B  C  D  E  F  G  H  I  J

. local vars `r(varlist)'

. foreach var of local vars {
  2.     local newname = `var'[1]
  3.     rename `var' `newname'
  4. }

. rename CIQ_ID date_str

. drop in 1/2
(2 observations deleted)

. // see what this looks like now
. list in 1/3, clean noobs

      date_str   IQ18511   IQ289194            IQ997111   IQ875418   IQ526391   
> IQ142742   IQ248270   IQ312375   IQ18527  
    12/31/2021     14.49     177.53               85.13      23650      219.3   
>    289.2      214.1     209.75     35.15  
    12/30/2021     14.66     178.87   85.90000000000001      23410      218.4   
>    289.8      213.1     209.25     35.15  
    12/29/2021     14.71     177.48               85.14      23350      218.7   
>    288.4      211.8     208.95        35  

. // change it from wide to long
. rename IQ* v_str=

. reshape long v_str, i(date_str) j(IQ) string
(j = IQ142742 IQ18511 IQ18527 IQ248270 IQ289194 IQ312375 IQ526391 IQ875418 IQ997
> 111)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations               24   ->   216         
Number of variables                  10   ->   3           
j variable (9 values)                     ->   IQ
xij variables:
v_strIQ142742 v_strIQ18511 ... v_strIQ997111-> v_str
-----------------------------------------------------------------------------

. // see what this looks like now
. describe

Contains data
 Observations:           216                  
    Variables:             3                  
--------------------------------------------------------------------------------
Variable      Storage   Display    Value
    name         type    format    label      Variable label
--------------------------------------------------------------------------------
date_str        str11   %11s                  
IQ              str8    %9s                   
v_str           str17   %17s                  
--------------------------------------------------------------------------------
Sorted by: date_str  IQ
     Note: Dataset has changed since last saved.

. // note that all variables are still strings 
. list in 1/9, clean noobs

      date_str         IQ    v_str  
     12/1/2021   IQ142742    271.4  
     12/1/2021    IQ18511   13.745  
     12/1/2021    IQ18527    32.09  
     12/1/2021   IQ248270    209.8  
     12/1/2021   IQ289194   171.54  
     12/1/2021   IQ312375   197.96  
     12/1/2021   IQ526391      203  
     12/1/2021   IQ875418    19935  
     12/1/2021   IQ997111    79.91  

. // change date from string to numeric Stata daily date
. generate date = daily(date_str,"MDY"), after(date_str)

. format date %td

. // change value from string to number
. destring v_str, generate(`dataitem')
v_str: all characters numeric; OpenPrice generated as double

. // let's see what this looks like
. list in 1/9, clean noobs abbreviate(32)

      date_str        date         IQ    v_str   OpenPrice  
     12/1/2021   01dec2021   IQ142742    271.4       271.4  
     12/1/2021   01dec2021    IQ18511   13.745      13.745  
     12/1/2021   01dec2021    IQ18527    32.09       32.09  
     12/1/2021   01dec2021   IQ248270    209.8       209.8  
     12/1/2021   01dec2021   IQ289194   171.54      171.54  
     12/1/2021   01dec2021   IQ312375   197.96      197.96  
     12/1/2021   01dec2021   IQ526391      203         203  
     12/1/2021   01dec2021   IQ875418    19935       19935  
     12/1/2021   01dec2021   IQ997111    79.91       79.91  

.

[Sunita Rao]

Dear Prof. Lisowski,

I cannot thank you enough for your kindness. Is there a comprehensive resource that I can use to familiarize myself with local macros and loops? That would be very useful for me.

[William Lisowski]

Chapter 18 "Programming Stata" in the Stata User’s Guide PDF included in your Stata Installation and accessible from Stata's Help menu is a good place to start.

At a more general level, when I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation.

The objective in doing the reading was not so much to master Stata - I'm still far from that goal, and falling further behind with every new release - as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

Stata also supples YouTube videos, if that's your thing.

[Sunita Rao]

Dear Prof. Lisowski,

I greatly appreciate your advice. I will get started with Chapter 18.