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
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;
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
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");
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;
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;
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.