Genius! Thanks very much!

-----Original Message----- From: Ya Huang [mailto:ya.huang@AMYLIN.COM] Sent: Monday, October 13, 2008 12:33 PM To: SAS-L@LISTSERV.UGA.EDU; Pardee, Roy Cc: Ya Huang Subject: Re: proc tabulate: N statistic does not take WEIGHT var into account?

Don't use weight, change N to sum, and colpctn to colpctsum:

proc tabulate format = comma10.2 ; class year agegroup drugcov ; var prorated_total ; keylabel sum = " " ; table drugcov="Drug Coverage?" * (agegroup all = "Subtotal"), year*prorated_total* (sum colPCTsum="%") ; run ;

--------------------------------------------------------------------- | | year | |--------------------------------------------- | | 1998 | 1999 | | |---------------------+---------------------+- | | prorated_total | prorated_total | | |---------------------+---------------------+- | | | % | | % | |----------------------+ ----------| ----------| |Drug |agegroup | | | | | |Coverage? | | | | | | |----------+-----------| | | | | |N |old | 1,095.83| 1.40| 1,717.70| 1.95| | |-----------+----------+----------+----------+----------+- | |young | 922.55| 1.17| 1,428.76| 1.63| | |-----------+----------+----------+----------+----------+- | |Subtotal | 2,018.38| 2.57| 3,146.46| 3.58| |----------+-----------+----------+----------+----------+----------+- |Y |agegroup | | | | | | |-----------| | | | | | |old | 39,451.57| 50.24| 42,016.02| 47.79| | |-----------+----------+----------+----------+----------+- | |young | 37,056.36| 47.19| 42,750.92| 48.63| | |-----------+----------+----------+----------+----------+- | |Subtotal | 76,507.93| 97.43| 84,766.94| 96.42|1 ---------------------------------------------------------------------

On Mon, 13 Oct 2008 12:20:29 -0700, Pardee, Roy <pardee.r@GHC.ORG> wrote:

>Hey All, > >I'm trying to use WEIGHT w/proc tabulate to get a table of Ns and percentages (taking the WEIGHT var into account), and am not able to figure out how to do it. Here's my data & call: > >* ==================================== ; > >data gnu ; > input > @1 year > @8 agegroup $char5. > @16 drugcov $char1. > @20 prorated_total > ; >datalines ; >1998 young N 922.55 >1998 young Y 37056.36 >1998 old N 1095.83 >1998 old Y 39451.57 >1999 young N 1428.76 >1999 young Y 42750.92 >1999 old N 1717.70 >1999 old Y 42016.02 >2000 young N 1990.79 >2000 young Y 56935.63 >2000 old N 4200.34 >2000 old Y 60000.99 >; >run ; > > >proc tabulate format = comma10.2 ; > class year agegroup drugcov ; > var prorated_total ; > keylabel sum = " " ; > table drugcov="Drug Coverage?" * (agegroup all = "Subtotal"), year * >(N colPCTN="%") ; > weight prorated_total ; >run ; > >* ==================================== ; > >It looks like PT is not taking the weight var into account--I'm getting: > >+-----------------------------------+---------------------------------- >+-----------------------------------+---- -----+ >| | year | >| >| +---------------------+---------------- -----+ >| | 1998 | 1999 | >| >| +----------+----------+----------+----- -----+ >| | N | % | N | % | >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- -----+ >|Drug Coverage? |agegroup | | | | | >+-----------------+-----------------+ | | | | >|N |old | 1.00| 25.00| 1.00| 25.00| >| >| +-----------------+----------+----------+----------+----- -----+ >| |young | 1.00| 25.00| 1.00| 25.00| >| >| +-----------------+----------+----------+----------+----- -----+ >| |Subtotal | 2.00| 50.00| 2.00| 50.00| >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- -----+ >|Y |agegroup | | | | | >| +-----------------+ | | | | >| |old | 1.00| 25.00| 1.00| 25.00| >| >| +-----------------+----------+----------+----------+----- -----+ >| |young | 1.00| 25.00| 1.00| 25.00| >| >| +-----------------+----------+----------+----------+----- -----+ >| |Subtotal | 2.00| 50.00| 2.00| 50.00| >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- -----+ > >(if that wraps & looks ugly, I've pasted it up here: http://pastie.org/291458.) > >What I really want is: > >+-----------------------------------+---------------------------------- >+-----------------------------------+---- -----+ >| | year | >| >| +---------------------+---------------- -----+ >| | 1998 | 1999 | >| >| +----------+----------+----------+----- -----+ >| | N | % | N | % | >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- -----+ >|Drug Coverage? |agegroup | | | | | >+-----------------+-----------------+ | | | | >|N |old | 1,095.83| 1.40| 1,717.70| 1.95| >| >| +-----------------+----------+----------+----------+----- -----+ >| |young | 922.55| 1.17| 1,428.76| 1.63| >| >| +-----------------+----------+----------+----------+----- -----+ >| |Subtotal | 2,018.38| 2.57| 3,146.46| 3.58| >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- -----+ >|Y |agegroup | | | | | >| +-----------------+ | | | | >| |old | 39,451.57| 50.24| 42,016.02| 47.79| >| >| +-----------------+----------+----------+----------+----- -----+ >| |young | 37,056.36| 47.19| 42,750.92| 48.63| >| >| +-----------------+----------+----------+----------+----- -----+ >| |Subtotal | 76,507.93| 97.43| 84,766.94| 96.42| >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- >+-----------------+-----------------+----------+----------+----------+- -----+ > >(or: http://pastie.org/291461) > >I've got a weirdo method for doing this, involving creating a phony var w/a constant value of 1, but I wondered if there was a better way. Is there? > >Thanks! > >-Roy > >Roy Pardee >Research Analyst/Programmer >Group Health Center For Health Studies (Cancer Research Network) >(206) 287-2078 >Google Talk: rpardee