No need of array:

proc sql; select distinct id, case when max(status)='D' and min(status)='D' then 1 else 0 end as count, case when max(status)='D' and min(status)='D' then 'D' else 'P' end as tag from one group by id order by id ;

id count tag ----------------------- 057800 0 P 505360 1 D 505361 0 P

On Tue, 30 Mar 2010 14:03:14 -0400, V V B <vibooks@COMCAST.NET> wrote:

>Hi All, > >I have a dataset with 3 fields: Id line_num and status. > >I need to read each line of an id and create a new dataset with three >fields id, tag and count, based on the value found in status. > >Beginning values: >Tag='P'; >Count=0; > >If status = D for every line for same id then tag='D' and count=1. > >I think I need sql code to collect max count and then an array to create >new fields. However, I don't know how to do it. > >My goal is to count the number of unique id's with a status of D. > >Sample data and expected output below. > >Your help is most appreciated! > >Thanks! > >Vivian > >data one; >input id line_num status; > >cards; >505360 1 D >505360 2 D >505360 3 D >505360 4 D >057800 1 D >057800 2 A >057800 3 A >057800 4 A >057800 5 P >057800 6 D >057800 7 D >057800 8 D >057800 9 D >057800 10 D >057800 11 D >057800 12 D >057800 13 D >057800 14 D >057800 15 D >057800 16 D >505361 1 D >505361 2 C >505361 3 C >505361 4 D > >; >run; > >data two; >input id tag ct; > >cards; > >505360 D 1 >057800 P 0 >505361 P 0 >Run;