topic Re: Numbering observations in SAS Programming

Numbering observations

RandyStan — Mon, 03 Dec 2018 04:43:16 GMT

Dear All:

My data set is as follows:

time ID

10:00:01 5

10:00:01 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: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: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:05 1467 2

10:00:06 1467 2

10:00:10 55

10:00:11 66

Please help

Thanx in Advance

Randy

Re: Numbering observations

novinosrin — Mon, 03 Dec 2018 05:01:14 GMT

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;

Re: Numbering observations

novinosrin — Mon, 03 Dec 2018 04:58:24 GMT

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

Re: Numbering observations

Kurt_Bremser — Mon, 03 Dec 2018 08:22:35 GMT

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");

Re: Numbering observations

Ksharp — Mon, 03 Dec 2018 12:30:13 GMT

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;

Re: Numbering observations

Astounding — Mon, 03 Dec 2018 14:24:07 GMT

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;

Re: Numbering observations

Kurt_Bremser — Mon, 03 Dec 2018 14:31:59 GMT

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.

Re: Numbering observations

RandyStan — Tue, 04 Dec 2018 00:13:00 GMT

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

Re: Numbering observations

Ksharp — Tue, 04 Dec 2018 12:46:19 GMT

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;