How do I move variable names to the next column?

[Rama Goda]

Hello,

I have around 35 economic variables for each frequency (Annual, Quarterly, Monthly, Weekly and Daily). However, the data source from where I pull this information (FRED) places the name of the variable on the year column and leaves a blank for the actual data column in excel, which is where I update the data on a regular basis. For now, I move the top row cells to the right in excel to ensure that the names of the variables correspond to the column with the corresponding data in it. However, I was wondering if there was a way to do it in stata? I tried looking it up online but have not found anything which offsets column name by one column. I can replace the variables but given that I have around 35 variables for each frequency, I was wondering if there was a command which could do it easily without me having to type replace for each and every column. And the names of the variables are also not very intuitive. Here is a sample table here to show how my data looks: (I am sorry, I am not able to install dataex to show it in the format that it is supposed to be in for posting questions.) My apologies in advance if this is messy but we have major issues with stata at work, where we are not able to install any user defined programs. Thanks in advance for your help!


Rama

GDPCA		GDPA		MEHOINUSA646N		RTFPNAUSA632NRUG
date	value	date	value	date	value	date	value
01/01/1929	1056.6	01/01/1929	104.6	01/01/1984	22415.0	01/01/1950	0.6
01/01/1930	966.7	01/01/1930	92.2	01/01/1985	23618.0	01/01/1951	0.6
01/01/1931	904.8	01/01/1931	77.4	01/01/1986	24897.0	01/01/1952	0.6
01/01/1932	788.2	01/01/1932	59.5	01/01/1987	26061.0	01/01/1953	0.6
01/01/1933	778.3	01/01/1933	57.2	01/01/1988	27225.0	01/01/1954	0.6
01/01/1934	862.2	01/01/1934	66.8	01/01/1989	28906.0	01/01/1955	0.6
01/01/1935	939.0	01/01/1935	74.3	01/01/1990	29943.0	01/01/1956	0.6

[Clyde Schechter]

So I think this will do what you need:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 gdpca str6 var2 str10 gdpa str5 var4 str10 mehoinusa646n str7 var6 str10 rtfpnausa632nrug str5 var8
"date"       "value"  "date"       "value" "date"       "value"   "date"       "value"
"01/01/1929" "1056.6" "01/01/1929" "104.6" "01/01/1984" "22415.0" "01/01/1950" "0.6"  
"01/01/1930" "966.7"  "01/01/1930" "92.2"  "01/01/1985" "23618.0" "01/01/1951" "0.6"  
"01/01/1931" "904.8"  "01/01/1931" "77.4"  "01/01/1986" "24897.0" "01/01/1952" "0.6"  
"01/01/1932" "788.2"  "01/01/1932" "59.5"  "01/01/1987" "26061.0" "01/01/1953" "0.6"  
"01/01/1933" "778.3"  "01/01/1933" "57.2"  "01/01/1988" "27225.0" "01/01/1954" "0.6"  
"01/01/1934" "862.2"  "01/01/1934" "66.8"  "01/01/1989" "28906.0" "01/01/1955" "0.6"  
"01/01/1935" "939.0"  "01/01/1935" "74.3"  "01/01/1990" "29943.0" "01/01/1956" "0.6"  
end

ds
local vbles `r(varlist)'

tokenize `vbles'

while "`1'" != "" {
    rename `1' `1'`=`1'[1]'
    rename `2' `1'`=`2'[1]'
    macro shift 2
}
drop in 1

Once that much is done, you will probably want to convert the date variables from stings to Stata internal format numerical dates, and you will want to -destring- the value variables. I would also imagine that for most purposes you would also be best off then changing from wide to long layout. So the following code:

foreach v of varlist *date {
    gen _`v' = daily(`v', "MDY") // OR DMY??
    assert missing(`v') == missing(_`v')
    order _`v', before(`v')
    drop `v'
    rename _`v' `v'
}
format *date %td

destring *value, replace

gen obs_no = _n
reshape long @date @value, i(obs_no) j(entity) string
drop obs_no

will tie it all up nicely and give you a data set that is ready for analysis in Stata.

[Rama Goda]

Thanks Clyde. Thank you so much for your prompt response...

[Rama Goda]

Clyde, I am sorry to bother you but I ran into an error when I run the code you gave for my entire data. So The table I showed was only a sample. When I put in all the data and run the code above, I get the following error message:


. ds
GDPCA GDPA MEHOINU~646N RTFPNAUSA6~G TWEXBANL AEXUSEU BPBLTT0~188S excel_row_~m
B D F H J L N

. local vbles `r(varlist)'

.
. tokenize `vbles'

.
. while "`1'" != "" {
2. rename `1' `1'`=`1'[1]'
3. rename `2' `1'`=`2'[1]'
4. macro shift 2
5. }
syntax error
Syntax is
rename oldname newname [, renumber[(#)] addnumber[(#)] sort ...]
rename (oldnames) (newnames) [, renumber[(#)] addnumber[(#)] sort ...]
rename oldnames , {upper|lower|proper}
r(198);

end of do-file

r(198);


Basically, I feel that when I have all the variables in there, it is putting the variables one below the other. (Basically here is what my variable names look like when I import them to stata:


GDPCA B GDPA D MEHOINUSA646N F RTFPNAUSA632N~G H TWEXBANL J AEXUSEU L BPBLTT01USA188S N


Not sure if that is what is causing the problem. Could you guide me please?

Thanks again!

Rama

[Clyde Schechter]

The way the code works is it goes through the variables, and then renames them to whatever is in the first observation for that variable. For the example data that worked fine. I suspect that for your complete data set some of those variables don't have a legal variable name in the first observation, which is causing the code to break.

I don't think there is any way to troubleshoot this without seeing the actual data set, or rather what's in the first observation, as well as the variable names.

So, if you don't already have the -dataex- command, get it by running -ssc install dataex-. Read -help dataex- to learn how to use it (it's easy). Then run

dataex in 1

and paste Stata's response here on the forum.

[Rama Goda]

Thanks Clyde. my problem is I am unable to install any prog to my stata at work. And that's why I am having to do what I am doing because I am unable to install Freduse, the Federal reserve software which imports data from the fed site directly to stata. So I installed their excel add-in which imports and updates the data and then I am trying to bring that in to stata in a format which I can use. Is there any way I can share the excel file with you?

Thanks again for your help.

[Rama Goda]

Here is the error message I get when I try to install dataex (my company has firewall issues which prevent installing user defined programs):

. ssc install dataex
connection timed out -- see help r(2) for troubleshooting
http://fmwww.bc.edu/repec/bocode/d/ either
1) is not a valid URL, or
2) could not be contacted, or
3) is not a Stata download site (has no stata.toc file).
r(2);

[Clyde Schechter]

Yes, your firewall is stopping you.

OK. I won't download an Excel file from a stranger: they can contain malware. Even if I decided to trust you, it's like a sexually transmitted disease: I'd have to trust everybody you interact with! But why don't you do this: just copy the first two rows of your Excel file into a new worksheet and then use SaveAs to save it as a CSV file. Then paste those two rows here in the forum between code delimiters. (See FAQ #12 if you don't know about code delimiters.) I can't guarantee that this will serve our purposes, because it is conceivable that what shows up from the CSV/paste operation will be different from what Stata is seeing when it imports directly from Excel, but it probably will be OK and it's worth a shot.

[Robert Picard]

Note that the only case where you can't use user-written programs from SSC is if the computer you use Stata on is not connected to the internet at all AND you are not allowed to bring with you files from the outside world and copy them to the computer.

Otherwise, you can get the files from the Boston College server using your favorite web browser. With respect to dataex, you can search google using the following search text:

repec dataex

and the first match is the following page. From there, you can download the program and the help file. These are plain text files that can be saved anywhere. When saving the files as plain text (or page source), make sure that you use the correct file names: "dataex.ado" and "dataex.sthlp".

As to where to put the files, type adopath in Stata's command window to find out where Stata looks for programs. If you have write access to the PERSONAL directory, put the ado and help file there. If not, put them in the directory listed under PLUS. Even if your Stata installation is quite restrictive and you do not have write access to any directories listed by the adopath command, you can still save the files in the directory that contains your do-files. Then use the "Change working directory..." File menu item and select the directory that contains the do-files you work on.

Once installed, you can verify that Stata can find the program by typing:

which dataex

Here's an example where dataex is installed in Stata's current directory:

. which dataex
./dataex.ado
*! 2.0.2 RP 23jun2016

. 
. sysuse sp500
(S&P 500)

. dataex in 1/5

----------------------- copy starting from the next line -----------------------
[CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input int date float(open high low close) double volume float change
14977 1320.28 1320.28 1276.05 1283.27 11294          .
14978 1283.27 1347.76 1274.62 1347.56 18807   64.29004
14979 1347.56 1350.24 1329.14 1333.34 21310 -14.220093
14980 1333.34 1334.77 1294.95 1298.35 14308  -34.98999
14983 1298.35 1298.35 1276.29 1295.86 11155   -2.48999
end
format %td date
[/CODE]
------------------ copy up to and including the previous line ------------------

Listed 5 out of 248 observations