Assign ID for a sequence

Accepted Solution Solved
Reply
Super Contributor
Posts: 420
Accepted Solution

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,041

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

View solution in original post


All Replies
Super Contributor
Posts: 420

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,041

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: 420

Re: Assign ID for a sequence

Thanks, Xia.

Your code works perfectly.

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

HHC

Super User
Posts: 10,041

Re: Assign ID for a sequence

Your own code is good as well. Smiley Happy

Respected Advisor
Posts: 3,156

Re: Assign ID for a sequence

I agree. not everybody can have a sophisticated brain like Ksharp does. Smiley Wink 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,041

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: 420

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;

Respected Advisor
Posts: 3,156

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,041

Re: Assign ID for a sequence

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

Super Contributor
Posts: 420

Re: Assign ID for a sequence

Hi Xia,

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

Please help me if you have time.

HHC

Super User
Posts: 10,041

Re: Assign ID for a sequence

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

Super Contributor
Posts: 420

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.

Need further help from the community? Please ask a new question.

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