Numbering observations

[RandyStan]

Dear All:

My data set is as follows:

time                                     ID

10:00:01                               5

10:00:01                               7

10:00:01                               7

10:00:02                               7

10:00:02                                7

10:00:02                            86

10:00:03                          145

10:00:03                           456

10:00:03                            97

10:00:04                           1467

10:00:04                           1467

10:00:04                            1467

10:00:05                            1467

10:00:05                            1467

10:00:05                            1467

10:00:06                            1467

10:00:10                             55

10:00:11                             66

The rule is that if the same ID appears consecutively at different times they are identified as 2

So the data  want looks as follows:

time                                     ID                                   BA

10:00:01                               5

10:00:01                               7                                      2

10:00:01                               7                                      2

10:00:02                               7                                      2

10:00:02                                7                                     2

10:00:02                            86

10:00:03                          145

10:00:03                           456

10:00:03                            97

10:00:04                           1467                                     2

10:00:04                           1467                                     2

10:00:04                            1467                                    2

10:00:05                            1467                                    2

10:00:05                            1467                                    2

10:00:05                            1467                                    2

10:00:06                            1467                                    2

10:00:10                             55

10:00:11                             66

Please help

Thanx in Advance

Randy

[novinosrin]

DATA HAVE;
input time :hhmmss10.                                    ID ;
format time time10.;
cards;
10:00:01                               5
10:00:01                               7
10:00:01                               7
10:00:02                               7
10:00:02                                7
10:00:02                            86
10:00:03                          145
10:00:03                           456
10:00:03                            97
10:00:04                           1467
10:00:04                           1467
10:00:04                            1467
10:00:05                            1467
10:00:05                            1467
10:00:05                            1467
10:00:06                            1467
10:00:10                             55
10:00:11                             66
;

data want;
set have;
by id time notsorted;
if first.id and last.id then call missing(ba);
else if first.id then ba=2;
retain ba;
run;

[novinosrin]

Btw, I do find some ambiguity with respect to your sample/question but anyway i'm already half asleep as it is too late here. I hope the above helps

[Kurt_Bremser]

The code can be simplified:

data want;
set have;
by id;
ba = ifn(first.id = last.id,.,2);
run;

If you want ba to be of type character, use the ifc() function:

ba = ifc(first.id = last.id," ","2");

[Ksharp]

DATA HAVE;
input time :hhmmss10.                                    ID ;
format time time10.;
cards;
10:00:01                               5
10:00:01                               7
10:00:01                               7
10:00:02                               7
10:00:02                                7
10:00:02                            86
10:00:03                          145
10:00:03                           456
10:00:03                            97
10:00:04                           1467
10:00:04                           1467
10:00:04                            1467
10:00:05                            1467
10:00:05                            1467
10:00:05                            1467
10:00:06                            1467
10:00:10                             55
10:00:11                             66
;
proc sql;
create table want as
 select *,case when count(distinct time)>1 then 2 else . end as flag
  from have
   group by id;
quit;

[Astounding]

I believe you can find flaws with all the suggestions so far.  Trickiest:  the @Ksharp suggestion will improperly flag non-consecutive blocks of observations for the same ID, with different times.  I would suggest:

data want;

do until (last.id);

   set have;

   by id notsorted;

   if first.id then first_one = time;

   if time ne first_one then ba=2;

end;

do until (last.id);

   set have;

   by id notsorted;

   output;

end;

drop first_one;

run;

[Ksharp]

Astounding,

Sharp eyes. I missed word  'consecutive' .

proc sql;
create table want as
 select *,case when count(distinct time)=range(time)+1 and count(distinct time)>1
 then 2 else . end as flag
  from have
   group by id;
quit;

[Kurt_Bremser]

Have to correct my suggestion:

data want;
set have;
by id notsorted;
ba = ifn(first.id and last.id,.,2);
run;

after usable example data was available.

[RandyStan]

Thanks all.

I plan to run it tonight.  I hope it excludes the case where all the ID's occur only during a second and not spillover into the other seconds.

   Randy