Date:         Fri, 22 Sep 2006 15:07:55 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Find and count unique group-records
Comments: To: tknilsso@GMAIL.COM
In-Reply-To:  <bc609f460609220755i4b412742jda4356908870340@mail.gmail.com>
Content-Type: text/plain; format=flowed

Thomas ,

Data Have ;
Infile Cards ;
Input ID HID WNO ;
Cards ;
1  22 323
2  22 323
3  22 324
4  33 667
5  33 668
6  44 789
;
run ;


Proc SQL ;
Create Table Need As
Select * , Count( Distinct WNO ) as WNOCnt
  From Have
   Group By HID
    Having WNOCnt >= 2 ;
Quit ;

Proc Print
Data = Need ;
Run ;



Toby Dunn

When everything is coming at you all at once, your in the wrong lane.

A truly happy person is someone who can smile and enjoy the scenery on a
detour.





From: Tomas Nilsson <tknilsso@GMAIL.COM>
Reply-To: Tomas Nilsson <tknilsso@GMAIL.COM>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Find and count unique group-records
Date: Fri, 22 Sep 2006 08:55:03 -0600

Dear SAS users;
In MS Excel there is the "Autofilter - unique records only" option, which is
great in identifying unique records and groups of unique records. My
question is: is there a function in SAS that can do this?

To give you an idea, here's an example of a household survey dataset:

ID HID WNO
1  22    323
2  22    323
3  22    324
4  33    667
5  33    668
6  44    789
where ID is the record identification marker which is unique for each row;
HID is a household identifier; and WNO the survey number. The WNO gives
you an opportunity to see how many survey's each household have completed.
So in this case, HID completed two surveys (no.s 323 and 324) but has two
recordings available for record no. 323; HID 33 completed two surveys
(no.s667 and 668); and HID completed just one (no. 789).

My question is, is there a function in SAS (other than in PROC IML) that I
can use to retrieve the following information: the number of different types
of surveys each household completed; no. recordings available for each
survey for each household. Say that I am only interested in households that
completed two surveys (i.e. HID 22 and 33), so how can I drop the others (
i.e. HID 44). I can do this in MS Excel but it's awfully cumbersome having
to continuously having to export and import data from one program into
another.

Thank you for your help in advance; I would appreciate any suggestions that
may point me to the right direction.

Tomas Nilsson