Howard, I am finding myself too busy of late to respond promptly to SAS-L messages, but I want to say that you nailed this problem squarely on the head. The data are organized in a manner that is all to common but often not very effective. If the data were stored in a more normalized fashion, e.g. with three columns - PERSON, YEAR, and RENTER, then the problem is simple - use the NOTSORTED option with a BY statement in PROC MEANS (or PROC SUMMARY) as you suggested. In this SAS community we seem to want our data in "flat files" where all of the variables for one entity are in a row. This often forces us to manage an unnecessarily large number of variables and can result in overly large, sparsely populated, data sets. Sometimes it makes analysis easier, but sometimes it makes it harder. I think we would all benefit by rethinking our table structures.

Ed

Edward Heaton, Senior Systems Analyst, Westat (An Employee-Owned Research Corporation), 1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195 Voice: (301) 610-4818 Fax: (301) 294-3992 mailto:EdwardHeaton@westat.com http://www.westat.com

-----Original Message----- From: Howard_Schreier@ITA.DOC.GOV [mailto:Howard_Schreier@ITA.DOC.GOV] Sent: Tuesday, March 26, 2002 4:21 PM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Counting in SAS

Here's a method which is 100% PROC.

Load data first:

data start; infile cards truncover;renter+1; input v1-v10; cards; 0 0 1 1 0 1 1 1 0 0 0 1 0 0 0 0 0 0 0 0 1 1 1 1 0 0 0 0 0 0 0 0 0 1 1 1 0 0 0 0 1 0 0 0 0 0 1 1 1 1 ;

Now a straightforward transpose:

proc transpose data=start out=long; by renter; var v1-v10; run;

Now use PROC SUMMARY to collapse the runs. Note the NOTSORTED option on the BY and the WHERE data set option on the output.

proc summary data=long nway; by renter col1 notsorted; output out=spells(where=(col1=1)); run;

Finally transpose to get back to one observation per household:

proc transpose data=spells out=finish(drop=_name_) prefix=spell; by renter; var _freq_; run;

Result:

Obs renter spell1 spell2

1 1 2 3 2 2 1 . 3 3 4 . 4 4 3 . 5 5 1 4

On Tue, 26 Mar 2002 14:20:18 -0500, Nathaniel Wooding <Nathaniel_Wooding@DOM.COM> wrote:

>Troy > >You have received a couple of good suggestions that use arrays. I often >offer a solution using proc transpose since this will handle any number of >new variables. > >Nat Wooding > >data raw; > infile cards truncover;renter+1; > input year1-year10; > cards; >0 0 1 1 0 1 1 1 0 0 >0 1 0 0 0 0 0 0 0 0 >1 1 1 1 0 0 0 0 0 0 >0 0 0 1 1 1 0 0 0 0 >1 0 0 0 0 0 1 1 1 1 >; >proc transpose out=one;by renter; >data one; >set; >by renter col1 notsorted; >if first.renter then v=0; >if first.col1 + col1 = 2 then do; > v+1; * v will set up the new variables titles; > count=0; >end; >if col1 then count+1; > else delete; >if last.col1 then output; > >proc transpose out=final prefix=var; > var count; > id v; > by renter; >proc print;run; > > > > Troy Jackson > <tyroc7@HOTMAI To: SAS-L@LISTSERV.UGA.EDU > L.COM> cc: > Sent by: Subject: Counting in SAS > "SAS(r) > Discussion" > <SAS-L@LISTSER > V.UGA.EDU> > > > 03/26/02 01:19 > PM > Please respond > to Troy > Jackson > > > > > > >I have a group of categorical variables that indicate if someone is a >renter >for a given year. Let's say there are 10 variables ar V1, V2, V3, V4,..V10 >representing 10 years. > >If a person is a renter the variables take on a value of 1, 0 otherwise. > >I would like count the length of each rental spell. For example, If someone >spent the first 3 years as a renter and the last 2 years as a renter, I >would like to have 2 variables one taking on a value of 3 representing the >first spell and one taking on a value of 2 representing the second spell. > >Any suggestions? > >_________________________________________________________________ >Chat with friends online, try MSN Messenger: http://messenger.msn.com