## Assign ID for a sequence

Solved
Super Contributor
Posts: 506

# Assign ID for a sequence

Hi Everyone,

I have a dataset with 2 variables as below.

I want to track a series from 3 to -3 and from -3 to 3.

The first 3 is in position 2 and the closest -3 is at position 7.

All record from 2 to 6 (RED)should carry the ID of the first 3, which is position 2.

For the next starting with -3 until it turn to 3 (BLUE), the ID should be 7.

The tricky part is the number 3 can appear multiple time within a sequence start with 3.

I am not sure if there is any quick method to assign the ID above.

I appreciate it if you could help me with that.

Many thanks,

HHC

*NOTE 1 is oldest;

data have;

input var1;

time=_n_;

datalines;

2

3

3

1

3

2

-3

-3

1

2

0

2

1

3

3

3

0

1

3

3

3

3

2

1

0

-3

1

0

-2

-3

2

3

Accepted Solutions
Solution
‎06-21-2014 08:47 AM
Super User
Posts: 10,787

## Re: Assign ID for a sequence

OK. Here is .

data have;
input var1;
time=_n_;
datalines;
2
3
3
1
3
2
-3
-3
1
2
0
2
1
3
3
3
0
1
3
3
3
3
2
1
0
-3
1
0
-2
-3
2
3
;
run;
data want(drop=n);
set have;
array x{0:1} _temporary_ (3 -3);
retain n 2 id;
if x{mod(n,2)}=var1 then do;
n+1;     id=time;
end;
run;

Xia Keshan

All Replies
Super Contributor
Posts: 506

## Re: Assign ID for a sequence

I do it with double retain.

data want; set have; *this one to create series of 3 or -3;
retain temp_ID;
if var1=-3 then temp_ID=var1;
else if var1=3 then temp_ID=var1;run;

data want; set want; *for each series of 3 or -3, keep the first ID;
retain ID;
lt=lag(temp_ID);
if temp_ID^=lt then ID=time;run;

Solution
‎06-21-2014 08:47 AM
Super User
Posts: 10,787

## Re: Assign ID for a sequence

OK. Here is .

data have;
input var1;
time=_n_;
datalines;
2
3
3
1
3
2
-3
-3
1
2
0
2
1
3
3
3
0
1
3
3
3
3
2
1
0
-3
1
0
-2
-3
2
3
;
run;
data want(drop=n);
set have;
array x{0:1} _temporary_ (3 -3);
retain n 2 id;
if x{mod(n,2)}=var1 then do;
n+1;     id=time;
end;
run;

Xia Keshan

Super Contributor
Posts: 506

## Re: Assign ID for a sequence

Thanks, Xia.

Somehow it is quite advance and I cannot understand how it process the data to get the result.

HHC

Super User
Posts: 10,787

## Re: Assign ID for a sequence

Your own code is good as well.

Posts: 3,167

## Re: Assign ID for a sequence

I agree. not everybody can have a sophisticated brain like Ksharp does. So here is my down to the ground solution. It is still one step one pass. and no need for 'time' variable and array() based binary switch.

data have;

input var1 @@;

/*time=_n_;*/

datalines;

2 3 3 1 3 2 -3 -3 1 2 0 2 1 3 3 3 0 1 3 3 3 3 2 1 0 -3 1 0 -2 -3 2 3

;

run;

data want1;

set have;

retain id _var;

if var1 in (3,-3) then

do;

_fst+1;

_t=_var+var1;

_var=var1;

end;

if _fst=1 or (var1 in (3,-3) and _t=0) then

id=_n_;

drop _:;

run;

Regards,

Haikuo

Super User
Posts: 10,787

## Re: Assign ID for a sequence

HaiKuo, Thanks your brag . No one can be perfect one .

Stay foolish, Stay hungry . -- from Steve Jobs

Xia Keshan

Super Contributor
Posts: 506

## Re: Assign ID for a sequence

Thank you, Haikuo and Xia.

I want to do the DO Loop to solve it but still cannot due to the fact that the number 3 can appear multiple time in a sequence.

Can you have a look and correct it for me?

HHC

I thought that the i=j would do the trick of skipping those var=3 but somehow it doesn't.

Should it because I have the I+1?

data want3;
set have nobs=nobs;
i+1;
if var1=3 then do;          *since var1=3 appear multiple time, the xtime doesn't hold value of the first var1=3;
xtime=time;

do j=i+1 to nobs until (found=1);
set have (rename=(var1=var2 time=time2)) point=j;
*here should be the "Skip";
if var2=-3 then do;
i=j;
found=1; end;
end;
end;
run;

Super Contributor
Posts: 275

## Re: Assign ID for a sequence

data want(drop=temp);

set have;

retain temp id;

if abs(var1)=3 and var1^=lag(var1) and var1^=temp then do;

id=_n_;

temp=var1;

end;

run;

Posts: 3,167

## Re: Assign ID for a sequence

Very good! You have my vote. This is probably as simple as it can get.

Haikuo

Super User
Posts: 10,787

## Re: Assign ID for a sequence

You can use your original solution. it is good enough.

Super Contributor
Posts: 506

## Re: Assign ID for a sequence

Hi Xia,

I really want to learn and use the Do Loop method.

HHC

Super User
Posts: 10,787

## Re: Assign ID for a sequence

Sorry I can't figure it out by your OD LOOP.

Super Contributor
Posts: 506

## Re: Assign ID for a sequence

Thanks Xia for checking it.

I still trying to break it for the learning purpose.

HHC

🔒 This topic is solved and locked.

Discussion stats
• 13 replies
• 409 views
• 4 likes
• 4 in conversation