Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: RE: How to use dataset where each record relates to a specific number of observations (weights?)

 From Eric Booth To "" Subject Re: st: RE: How to use dataset where each record relates to a specific number of observations (weights?) Date Sun, 29 May 2011 04:21:44 +0000

```<>
Hi Nick--
For question #1 (expanding observations based on var 'number'), you can use the -expand- command to create your dataset, then use -table-, etc to describe the dataset.
For question #2 (exporting table to excel), here are 3 solutions -- you can use -table-s 'replace' option and -outsheet- the results, use -logout- (from SSC) to get the -table- result into excel, or use -tabout- (from SSC).  An example of each of these suggestions is included in the example below.

**************************!
**install tabout and logout from SSC
foreach t in tabout logout {
cap which `t'
if _rc ssc install `t', replace
}

**create fake data**
clear
inp number str5(coub) country nat
2 A 1 0
3 A 1 1
4 A 3 1
2 A 3 0
1 A 2 1
1 A 2 0
4 B 1 1
1 B 3 0
1 B 3 1
2 C 1 1
6 C 1 0
3 C 9 0
end
expand number
bys nat, sort: tab coub country

**using table
recode nat (1=100) (2=0), generate(nat2)
preserve
table coub country, contents(mean nat2) replace
outsheet using "table1.xls", replace
restore

**better formatting for using -table, replace- w/logout(from SSC)
logout, save("table2") excel replace: table coub country, ///
contents(mean nat2) stubwidth(25)

tabout coub country using "table3.xls", replace sum c(mean nat2) f(2p) ///
**************************!
Open Tables 1 - 3 in Excel.

- Eric
__
Eric A. Booth
Public Policy Research Institute
Texas A&M University
ebooth@ppri.tamu.edu

On May 28, 2011, at 10:45 PM, Nick Petschek wrote:

> Thank you, David.  Worked magically.  However I was unable to copy it
> into excel without formating issues (neither copying "as table" or
> text import in excel worked).
>
> Nick
>
> On Thu, May 26, 2011 at 9:16 PM, David Radwin <dradwin@mprinc.com> wrote:
>> Nick,
>>
>> 1. It is weighting. Add [fweight=NUMBER] to your commands in the
>> appropriate place, typically immediately before the comma that signifies
>> options. See -help weight- for more on this.
>>
>> 2. Try this:
>>
>> . recode NAT (1=100) (2=0), generate(NAT2)
>> . table COUB COUNTRY, contents(mean NAT2) format(%9.0f)
>>
>> If you want percentage signs and/or Excel-friendly output, try installing
>> and using Ian Watson's -tabout- from SSC. A nice PDF help file is also
>> available.
>>
>>
>> David
>> --
>> Research Associate
>> MPR Associates, Inc.
>> 2150 Shattuck Ave., Suite 800
>> Berkeley, CA 94704
>> Phone: 510-849-4942
>> Fax: 510-849-0794
>>
>> www.mprinc.com
>>
>>
>>> -----Original Message-----
>>> From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-
>>> statalist@hsphsun2.harvard.edu] On Behalf Of Nick Petschek
>>> Sent: Thursday, May 26, 2011 5:57 PM
>>> To: statalist@hsphsun2.harvard.edu
>>> Subject: st: How to use dataset where each record relates to a specific
>>> number of observations (weights?)
>>>
>>> Dear Statalist,
>>>
>>> I have two (basic) questions.  I would very much appreciate direction
>>> on either or both!
>>>
>>> 1. How do I use a dataset where each record (row) is representing more
>>> than one observation?  Specifically, there is a variable NUMBER which
>>> denotes how many observations the record refers to.  Abstractly I
>>> understand that each record would just need to be multiplied by
>>> NUMBER.  At the moment I am only looking to run cross-tabs but do not
>>> understand how to get STATA to incorporate the variable NUMBER (which
>>> I believe could be similar to weighting?).
>>>
>>> For example, I want the naturalization rates of foreign born
>>> populations using NAT (1 if naturalized 2 if not) COUB (country of
>>> birth) and COUNTRY (country of residence) to find the percent of each
>>> foreign born group naturalized in each host country.  I have run:
>>>
>>> by NAT, sort : tab COUB COUNTRY
>>>
>>> However this does not capture the fact that each record refers to a
>>> distinct number of observations.
>>>
>>>
>>>
>>> 2. I am sure there must be a more straightforward way to obtain the
>>> results I am looking for, with the above code I have been exporting to
>>> excel and then doing the final percent calculation.  Is there a more
>>> straightforward way to tell STATA what I want?  Ideally I want my
>>> results to look like this:
>>>
>>>                  COUNTRY
>>> COUB          1            2            3
>>> A              %nat      %nat      %nat
>>> B              %nat      %nat      %nat
>>> C              %nat      %nat      %nat
>>>
>>>
>>>
>>> Many thanks for advice or direction,
>>>
>>> Nick
>>> nick.petschek@gmail.com
>>>
>>> --
>>> Nicholas S. Petschek
>>> Master's Candidate | Tufts University
>>> Urban and Environmental Policy and Planning | 2012
>>> MALD at The Fletcher School | 2012
>>> *
>>> *   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/
>>
>
>
>

*
*   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/
```