means in long format
in [SAS]
|
Prev: New SAS product
Next: dumping memory
From: Joe Matise on 17 Feb 2010 14:39 data have; input SUBJID $ TEST $ VALUE1 VALUE2; datalines; A1 ONE 3 1 A1 TWO 2 2 A1 THREE 6 5 A2 ONE 4 3 A2 TWO 5 4 A2 THREE 8 9 ;;;; run; data want(keep=subjid value1 value2 test); do _n_ = 1 by 1 until (last.subjid); set have; by subjid; if first.subjid then do; meanval1=0; meanval2=0; counter=0; end; if test in ("ONE","TWO") then do; meanval1+value1; meanval2+value2; counter+1; end; end; do _n_ = 1 by 1 until (last.subjid); set have; by subjid; output; if last.subjid then do; value1=meanval1/counter; value2=meanval2/counter; test="MEAN"; output; end; end; run; -Joe On Wed, Feb 17, 2010 at 1:33 PM, W <wcw2(a)cdc.gov> wrote: > I have data in the following "long" format (obs=test, three tests per > subject) > > SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > > I would like to add one observation per subject, > which would be the means of tests ONE and TWO, giving me the following: > > SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A1 MEAN 2.5 1.5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > A2 MEAN 4.5 3.5 > > I can do this easily when I convert the data into a "wide" format (obs=ID) > using the MEANS function, but I need to try & retain the "long" format. > I've tried a number of things, none of which works well, can anyone offer > any suggestions? (Some subjects may be missing some tests, & so there may > not be 3 complete observations per subject). > > Thanks! >
From: "Data _null_;" on 17 Feb 2010 14:45 I'll contribute one more for good measure. data test; input SUBJID:$2. TEST:$5. VALUE1 VALUE2; cards; A1 ONE 3 1 A1 TWO 2 2 A1 THREE 6 5 A2 ONE 4 3 A2 TWO 5 4 A2 THREE 8 9 ;;;;; run; proc summary data=test nway; class subjid; where test in('ONE' 'TWO'); output out=means(index=(subjid) drop=_:) mean(value:)=; run; data test; do until(last.subjid); set test; by subjid; output; end; test = 'MEAN'; set means key=subjid; output; run; proc print; run; On 2/17/10, W <wcw2(a)cdc.gov> wrote: > I have data in the following "long" format (obs=test, three tests per > subject) > > SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > > I would like to add one observation per subject, > which would be the means of tests ONE and TWO, giving me the following: > > SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A1 MEAN 2.5 1.5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > A2 MEAN 4.5 3.5 > > I can do this easily when I convert the data into a "wide" format (obs=ID) > using the MEANS function, but I need to try & retain the "long" format. > I've tried a number of things, none of which works well, can anyone offer > any suggestions? (Some subjects may be missing some tests, & so there may > not be 3 complete observations per subject). > > Thanks! >
From: Ya Huang on 17 Feb 2010 14:41 This should do: proc sql; create table need as select * from have group by subjid outer union corr select distinct subjid, 'MEAN' as test, mean(value1) as value1, mean(value2) as value2 from have group by subjid order by subjid, test ; On Wed, 17 Feb 2010 14:33:09 -0500, W <wcw2(a)CDC.GOV> wrote: >I have data in the following "long" format (obs=test, three tests per >subject) > >SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > >I would like to add one observation per subject, >which would be the means of tests ONE and TWO, giving me the following: > >SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A1 MEAN 2.5 1.5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > A2 MEAN 4.5 3.5 > >I can do this easily when I convert the data into a "wide" format (obs=ID) >using the MEANS function, but I need to try & retain the "long" format. >I've tried a number of things, none of which works well, can anyone offer >any suggestions? (Some subjects may be missing some tests, & so there may >not be 3 complete observations per subject). > >Thanks!
From: Joe Matise on 17 Feb 2010 14:44 If I understand the OP correctly, and your solution correctly, you need to throw in a where test in ("ONE","TWO") in the second select. -Joe On Wed, Feb 17, 2010 at 1:41 PM, Ya Huang <ya.huang(a)amylin.com> wrote: > This should do: > > proc sql; > create table need as > select * > from have > group by subjid > outer union corr > select distinct subjid, 'MEAN' as test, > mean(value1) as value1, mean(value2) as value2 > from have > group by subjid > order by subjid, test > ; > > On Wed, 17 Feb 2010 14:33:09 -0500, W <wcw2(a)CDC.GOV> wrote: > > >I have data in the following "long" format (obs=test, three tests per > >subject) > > > >SUBJID TEST VALUE1 VALUE2 > > A1 ONE 3 1 > > A1 TWO 2 2 > > A1 THREE 6 5 > > A2 ONE 4 3 > > A2 TWO 5 4 > > A2 THREE 8 9 > > > >I would like to add one observation per subject, > >which would be the means of tests ONE and TWO, giving me the following: > > > >SUBJID TEST VALUE1 VALUE2 > > A1 ONE 3 1 > > A1 TWO 2 2 > > A1 THREE 6 5 > > A1 MEAN 2.5 1.5 > > A2 ONE 4 3 > > A2 TWO 5 4 > > A2 THREE 8 9 > > A2 MEAN 4.5 3.5 > > > >I can do this easily when I convert the data into a "wide" format (obs=ID) > >using the MEANS function, but I need to try & retain the "long" format. > >I've tried a number of things, none of which works well, can anyone offer > >any suggestions? (Some subjects may be missing some tests, & so there may > >not be 3 complete observations per subject). > > > >Thanks! >
From: NordlDJ on 17 Feb 2010 14:48
> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of W > Sent: Wednesday, February 17, 2010 11:33 AM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: means in long format > > I have data in the following "long" format (obs=test, three tests per > subject) > > SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > > I would like to add one observation per subject, > which would be the means of tests ONE and TWO, giving me the following: > > SUBJID TEST VALUE1 VALUE2 > A1 ONE 3 1 > A1 TWO 2 2 > A1 THREE 6 5 > A1 MEAN 2.5 1.5 > A2 ONE 4 3 > A2 TWO 5 4 > A2 THREE 8 9 > A2 MEAN 4.5 3.5 > > I can do this easily when I convert the data into a "wide" format (obs=ID) > using the MEANS function, but I need to try & retain the "long" format. > I've tried a number of things, none of which works well, can anyone offer > any suggestions? (Some subjects may be missing some tests, & so there may > not be 3 complete observations per subject). > > Thanks! Something like this should work data have; input SUBJID $ TEST $ VALUE1 VALUE2; cards; A1 ONE 3 1 A1 TWO 2 2 A1 THREE 6 5 A2 ONE 4 3 A2 TWO 5 4 A2 THREE 8 9 ; run; data want(drop=one1 one2 two1 two2); set have; by subjid; if test EQ 'ONE' then do; one1 = value1; one2 = value2; end; if test EQ 'TWO' then do; two1 = value1; two2 = value2; end; retain one1 one2 two1 two2; output; if last.subjid then do; test = 'MEAN'; value1 = mean(one1,two1); value2 = mean(one2,two2); output; end; run; Hope this is helpful, Dan Daniel J. Nordlund Washington State Department of Social and Health Services Planning, Performance, and Accountability Research and Data Analysis Division Olympia, WA 98504-5204 |