Hi Ric,
I think the issue might be that you have two ID variables in your dataset (orientation and orientation1). When you choose one as your j variable in the -reshape-, Stata doesn't know what to do with the other variable after reshaping.
Try the following:
Drop orientation
collapse (sum) congregations-attendeepct, by(orientation1 metrocode metroshortname metropop2000)
reshape wide congregations members adherents attendees adherentpct attendeepct, i(metrocode metroshortname metropop2000) j(orientation1)
You can use -decode- to recreate orientation afterwards if you'd like. If this doesn't work, I'm stumped.
Hope this helps.
Howie
Howie Lempel
Research Assistant
The Brookings Institution | Economic Studies
1775 Massachusetts Ave NW | Washington DC 20036
[email protected] | p: (202) 238-3576
-----Original Message-----
From: [email protected] [mailto:[email protected]] On Behalf Of Eric Uslaner
Sent: Sunday, March 15, 2009 10:37 AM
To: [email protected]
Subject: st: Re: reshape/collapse question (alas, again)
I sent this post to the list and got a nice reply from Howie Lempel. Here is the post and the reply and the persistent problem that I do not understand:
>>> Eric Uslaner wrote:
Hi,
I have a somewhat complex merge/collapse question that I could not find the answer to in the manuals or with help.
I have two data sets. One is the main data set with data on metropolitan areas (SMSAs) sorted by SMSA.
The second data set is religious membership and attendance by denomination (denomcode and denomname) and by a coding I have used for the orientation of each denomination. This latter data set contains:
. . .
There are several hundred denominations but I don't care about the denominations per se, just the orientations (Catholic, Eastern, CNT--conservative non-traditional, evangelical, Jewish, Mainline, Orthodox) which are in the string variable orientation and the decoded numericl variable orientation1. The data are arrayed with one observation for each denomination for each metropolitan area (e.g., Catholics in Amarillo would be one observation, LDS in Chicago another, etc.). What I want to do is to sum congregations through attendeepct for each orientation by metropolitan area, so that one record would be the number of congregations, members, adherents, etc. for Catholics in Chicago (e.g.), the number of congregations etc. for followers of Eastern religions, ..., the number of congregations... for Orthodox followers in Chicago). There should be one observation per city (here metrocode--which I will rename smsa before merging with the master data set).
Here is Howie's response:
Try something like this:
collapse (sum) congregations-attendeepct, by(orientation orientation1 metrocode metroshortname metropop2000)
reshape wide congregations members adherents attendees adherentpct attendeepct, i(metrocode metroshortname metropop2000) j(orientation1)
The collapse worked fine, but the reshape gave me once more:
(note: j = . CATH CNT EAST EV JEW ML OR)
orientation1 not constant within metrocode metroshortname metropop2000
Is there a solution to this problem? Thanks much.
Ric
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
