Date: Wed, 28 Oct 2009 10:33:45 0700
ReplyTo: Akshaya <akshaya.nathilvar@GMAIL.COM>
Sender: "SAS(r) Discussion" <SASL@LISTSERV.UGA.EDU>
From: Akshaya <akshaya.nathilvar@GMAIL.COM>
Subject: Re: Is there a function to count the number of distinct
nonmissing values in an array?
InReplyTo: <456B52C41B724C41B96561D7AD283E7D01F049CD@mail.chpdm.umbc.edu>
ContentType: text/plain; charset=ISO88591
Here's a solution using Datastep and SQL:
data have;
infile cards missover;
input id (v1v5) ($);
_n=_n_;
cards;
1 A A A A A
1 A A
1 A A
2 A A A A B
2 A B B B
2 B B C
3 A B B C
3 B C D B
4 A B C D
4 B B C D E
5 A B C D E
;
Data havee;
set have;
by id _n;
array vv v:;
do over vv;
var=vv;
output;
end;
Run;
Proc sql;
create table want(drop=_n) as
select _n,id,v1,v2,v3,v4,v5,count(distinct var) as count
from havee
group by 1,2,3,4,5,6,7
order by 1,2,3,4,5,6,7;
Quit;
AkshayA!
On Wed, Oct 28, 2009 at 8:23 AM, Jack Clark <jclark@hilltop.umbc.edu> wrote:
> Nat,
>
> No, not much response yet. A coworker suggested trying an array with a
> nested DO loop to compare each array element to those before it. I've
> been playing around with it and my current "solution" is below. For
> ease of checking the output in my sample data, the value of ID is also
> the expected value of the COUNT variable.
>
> I would welcome critique from anyone on the list. Thanks.
>
>
> data have;
> input @01 id $1.
> @03 icn1 $1.
> @05 icn2 $1.
> @07 icn3 $1.
> @09 icn4 $1.
> @11 icn5 $1.
> ;
> cards;
> 1 A A A A A
> 1 A A
> 1 A A
> 2 A A A A B
> 2 A B B B
> 2 B B C
> 3 A B B C
> 3 B C D B
> 4 A B C D
> 4 B B C D E
> 5 A B C D E
> ;
> run;
>
>
> data need;
> set have;
> array icn (5) $ icn1icn5;
>
> * initialize counter ;
> count = 0;
>
> do i = 1 to dim(icn);
>
> * first array element does not do comparisons ;
> if i = 1 then do;
> if icn(i) ne ' ' then count + 1;
> end;
>
> * subsequent array elements are compared to prior elements ;
> else do;
> notnew = 0;
> if icn(i) ne ' ' then do;
> do j = (i1) to 1 by 1;
> if icn(i) = icn(j) then notnew + 1;
> end;
> if notnew eq 0 then count + 1;
> end;
> end;
>
> end;
> run;
>
> proc print data = need;
> run;
>
>
>
>
>
>
>
> Jack Clark
> Senior Research Analyst
> phone: 4104556256
> fax: 4104556850
> jclark@hilltop.umbc.edu Original Message
> From: Nathaniel Wooding [mailto:nathaniel.wooding@dom.com]
> Sent: Wednesday, October 28, 2009 10:31 AM
> To: Jack Clark
> Subject: RE: Is there a function to count the number of distinct
> nonmissing values in an array?
>
> Jack
>
> I have not seen a flood of replies. My typical approach here would be to
> do a transpose.
>
> Nat Wooding
>
> Original Message
> From: SAS(r) Discussion [mailto:SASL@LISTSERV.UGA.EDU] On Behalf Of
> Jack Clark
> Sent: Wednesday, October 28, 2009 8:51 AM
> To: SASL@LISTSERV.UGA.EDU
> Subject: Is there a function to count the number of distinct nonmissing
> values in an array?
>
> Good morning,
>
>
>
> I have a data set with character variables like var1var50. Not all
> records have values in all 50 variables. For each record, I would like
> to know the number of distinct, nonmissing values across the 50
> variables. My ultimate goal is to have a frequency report of this count
> so I know that x number of obs. in the data set have 1 unique value, y
> number of obs. in the data set have 2 unique values, etc.
>
>
>
> I have thought of switching from my short and wide data set structure to
> a tall and long one (with transpose or data step), then that may make
> counting distinct values easier (maybe with PROC SQL). But I was
> wondering if anyone knows of some clever data step method or array
> function (?) that would work.
>
>
>
> Thanks in advance.
>
>
>
>
> Jack
>
>
>
> Jack Clark
> Senior Research Analyst
> phone: 4104556256
> fax: 4104556850
> jclark@hilltop.umbc.edu
>
> University of Maryland, Baltimore County
> Sondheim Hall, 3rd Floor
> 1000 Hilltop Circle
> Baltimore, MD 21250
>
>
>
>
> Confidentiality Notice: This email may contain information that is
> legally privileged and that is intended only for the use of the
> addressee(s) named above. If you are not the intended recipient, you are
> hereby notified that any disclosure, copying of this email,
> distribution, or action taken in reliance on the contents of this email
> and/or documents attributed to this email is strictly prohibited. If
> you have received this information in error, please notify the sender
> immediately by phone and delete this entire email. Thank you.
> CONFIDENTIALITY NOTICE: This electronic message contains
> information which may be legally confidential and or privileged and
> does not in any case represent a firm ENERGY COMMODITY bid or offer
> relating thereto which binds the sender without an additional
> express written confirmation to that effect. The information is
> intended solely for the individual or entity named above and access
> by anyone else is unauthorized. If you are not the intended
> recipient, any disclosure, copying, distribution, or use of the
> contents of this information is prohibited and may be unlawful. If
> you have received this electronic transmission in error, please
> reply immediately to the sender that you have received the message
> in error, and delete it. Thank you.
>
