Manipulating observation 1

Budu Gulo

Join Date: Feb 2018
Posts: 235

Manipulating observation 1

31 Aug 2018, 09:57

Code:

     +--------------------------------------------+
     |  var1        var2   var3     var4     var5 |
     |--------------------------------------------|
  1. |  Name        Code   2016     2017     2018 |
  2. | APPLE   992816(P)    106   163.35   225.03 |
     +--------------------------------------------+

I want to do two things:

1. For var3-5, I want to add "Y" to observation 1. It should look like the following.

Code:

      +---------------------------------------------+
     |  var1        var2    var3     var4     var5 |
     |---------------------------------------------|
  1. |  Name        Code   Y2016    Y2017    Y2018 |
  2. | APPLE   992816(P)     106   163.35   225.03 |
     +---------------------------------------------+

2. I then want to delete observation 1 and consider it as variable names. New data should look like the following.

Code:

     +---------------------------------------------+
     |  Name        Code   Y2016    Y2017    Y2018 |
     |---------------------------------------------|
  1. | APPLE   992816(P)     106   163.35   225.03 |
     +---------------------------------------------+

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str53 var1 str15 var2 float(var3 var4 var5)
"Name"                                                  "Code"                 2016      2017     2018
"APPLE"                                                 "992816(P)"             106    163.35   225.03
"APPLE - MARKET VALUE"                                  "992816(MV)"       571174.8  843739.3  1086877
"APPLE - MARKET CAPITALIZATION (U.S.$)"                 "992816(WC07210)" 618034752 867507008        .
"APPLE - NET INCOME (U.S.$)"                            "992816(WC07250)"  45687000  48351000        .
"MICROSOFT"                                             "719643(P)"           57.89     74.01   111.95
"MICROSOFT - MARKET VALUE"                              "719643(MV)"       451108.6  570042.8 858456.7
"MICROSOFT - MARKET CAPITALIZATION (U.S.$)"             "719643(WC07210)" 485189120 659342336        .
"MICROSOFT - NET INCOME (U.S.$)"                        "719643(WC07250)"  16798000  21204000 16571000
"JP MORGAN CHASE & CO."                                 "902242(P)"            67.5     91.31   115.19
"JP MORGAN CHASE & CO. - MARKET VALUE"                  "902242(MV)"       243808.6  321316.4 387140.1
"JP MORGAN CHASE & CO. - MARKET CAPITALIZATION (U.S.$)" "902242(WC07210)" 307295936 366301568        .
"JP MORGAN CHASE & CO. - NET INCOME (U.S.$)"            "902242(WC07250)"  24230000  24230000        .
"JOHNSON & JOHNSON"                                     "912212(P)"          119.47    131.07   134.95
"JOHNSON & JOHNSON - MARKET VALUE"                      "912212(MV)"       326854.9  351791.7   362038
"JOHNSON & JOHNSON - MARKET CAPITALIZATION (U.S.$)"     "912212(WC07210)" 311817120 374802400        .
"JOHNSON & JOHNSON - NET INCOME (U.S.$)"                "912212(WC07250)"  16540000   1300000        .
end

Tags: None

Clyde Schechter

Join Date: Apr 2014

Posts: 28624
#2

31 Aug 2018, 10:17

Code:

foreach v of varlist _all { capture confirm string var `v' if c(rc) == 0 { local vname = strtoname(`v'[1]) } else { local vname = strtoname(string(`v'[1])) } rename `v' `vname' } rename _* y* drop in 1

Note that this will leave you with a wide data set, and with your actual variables buried in the "name" variable. So you will want to pull the variables out of the name variable (as you have seen how to do in a different thread you starrted), and then you will want to do two -reshape- operations to get the actual variables to be variables and get the year to be a separate variable so you have real panel data.
1 like
Comment
Budu Gulo

Join Date: Feb 2018

Posts: 235
#3

31 Aug 2018, 10:31

Thank you so much Clyde Schechter. You are an inspiration!
Comment

Budu Gulo

Join Date: Feb 2018
Posts: 235

08 Jan 2019, 18:42

Code:

     +------------------------------------------------------------+
     |          A           B           C           D           E |
     |------------------------------------------------------------|
  1. |       Code   68372V(P)   138056(P)   139409(P)   9445HE(P) |
  2. |   1/1/2014         202      1595.2       116.8          NA |
     +------------------------------------------------------------+

Similar problem. This time instead of years e.g. 2016, observation 1 contains identifiers e.g. 68372V(P). The code provided in #2 works well when I apply it on limited number of variables.

However, I get the following error when I apply the code to full data set.

Code:

. foreach v of varlist _all {
  2.     capture confirm string var `v'
  3.     if c(rc) == 0 {
  4.         local vname = strtoname(`v'[1])
  5.     }
  6.     else {
  7.         local vname = strtoname(string(`v'[1]))
  8.     }
  9.     rename `v' `vname'
 10. }
syntax error
    Syntax is
        rename  oldname    newname   [, renumber[(#)] addnumber[(#)] sort ...]
        rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
        rename  oldnames              , {upper|lower|proper}
r(198);

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 A str12 B str14 C str22 D str16 E
"Code"       "68372V(P)" "138056(P)" "139409(P)" "9445HE(P)"
"  1/1/2014" "202"       "1595.2"    "116.8"     "NA"       
"  1/2/2014" "206.3"     "1629.1"    "119.8"     "NA"       
"  1/3/2014" "206.3"     "1629.1"    "119.8"     "NA"       
"  1/6/2014" "206.4"     "1629.9"    "119.19"    "NA"       
"  1/7/2014" "207.7"     "1640.8"    "120.22"    "NA"       
"  1/8/2014" "210.5"     "1661.6"    "122.06"    "NA"       
"  1/9/2014" "208.7"     "1651.9"    "123.65"    "NA"       
" 1/10/2014" "209.9"     "1640"      "125.6"     "NA"       
" 1/13/2014" "205.9"     "1720.5"    "126.52"    "NA"       
" 1/14/2014" "205.9"     "1720.5"    "126.52"    "NA"       
end

Comment

Clyde Schechter

Join Date: Apr 2014
Posts: 28624

08 Jan 2019, 18:54

I am unable to duplicate this problem using your example data and the code shown:

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 A str12 B str14 C str22 D str16 E
"Code"       "68372V(P)" "138056(P)" "139409(P)" "9445HE(P)"
"  1/1/2014" "202"       "1595.2"    "116.8"     "NA"       
"  1/2/2014" "206.3"     "1629.1"    "119.8"     "NA"       
"  1/3/2014" "206.3"     "1629.1"    "119.8"     "NA"       
"  1/6/2014" "206.4"     "1629.9"    "119.19"    "NA"       
"  1/7/2014" "207.7"     "1640.8"    "120.22"    "NA"       
"  1/8/2014" "210.5"     "1661.6"    "122.06"    "NA"       
"  1/9/2014" "208.7"     "1651.9"    "123.65"    "NA"       
" 1/10/2014" "209.9"     "1640"      "125.6"     "NA"       
" 1/13/2014" "205.9"     "1720.5"    "126.52"    "NA"       
" 1/14/2014" "205.9"     "1720.5"    "126.52"    "NA"       
end

foreach v of varlist _all {
    capture confirm string var `v'
    if c(rc) == 0 {
        local vname = strtoname(`v'[1])
    }
    else {
        local vname = strtoname(string(`v'[1]))
    }
    rename `v' `vname'
}

des

runs just fine on my setup.

Comment

Budu Gulo

Join Date: Feb 2018

Posts: 235
#6

08 Jan 2019, 19:10

Actually, with this example data it runs fine. I could not post the problematic data due to linesize limit. Fortunately, I think I have spotted the issue. The problem starts at BG, where the cell is empty. There are many cases like this. What would be your approach to tackle this issue?
Comment

Clyde Schechter

Join Date: Apr 2014
Posts: 28624

08 Jan 2019, 19:21

I would do this:

Code:

foreach v of varlist _all {
    if !missing(`v'[1]) {
        capture confirm string var `v'
        if c(rc) == 0 {
            local vname = strtoname(`v'[1])
        }
        else {
            local vname = strtoname(string(`v'[1]))
        }
        rename `v' `vname'
    }
}

This way the code will skip over BG and just leave its name as BG.

Comment

Budu Gulo

Join Date: Feb 2018

Posts: 235
#8

08 Jan 2019, 19:32

It works perfectly! I would like to delete variables like BG. How could I delete them without spotting manually ?
Comment
Clyde Schechter

Join Date: Apr 2014

Posts: 28624
#9

08 Jan 2019, 19:35

Code:

foreach v of varlist _all { if !missing(`v'[1]) { capture confirm string var `v' if c(rc) == 0 { local vname = strtoname(`v'[1]) } else { local vname = strtoname(string(`v'[1])) } rename `v' `vname' } else { drop `v' } }

Just one caution: this code is just looking at whether or not the first cell in that spreadsheet column was empty. It does not automatically follow that the entire column is empty. So you might want to look into that before you just go ahead and delete variables like BG--there may be information further down that you want to keep.
1 like
Comment
Budu Gulo

Join Date: Feb 2018

Posts: 235
#10

08 Jan 2019, 19:39

The problem is solved. Thanks a lot Clyde Schechter !
Comment

Announcement

Manipulating observation 1

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment