Solved
Contributor
Posts: 35

# Finding number of times changing id

Hi,

please can anyone help me the follwing probelm.

Here the dummy data;

data have;
input id num;
cards;
1 1
1 2
1 1
1 2
1 1
1 2
2 1
2 15
2 1
2 1
2 1
2 15
2 1
2 15
;
run;

How to count number of times num (column) is changing for each id ?

please find the results and new column.

Reults:

id number no_of_times
1 1 1
1 2 1
1 1 1
1 2 2
1 1 1
1 2 3
2 1 1
2 15 1
2 1 1
2 1 1
2 1 1
2 15 2
2 1 1
2 15 3
;

Accepted Solutions
Solution
‎08-01-2016 05:22 PM
Super Contributor
Posts: 340

## Re: Finding number of times changing id

Hello,

Not sure I understand the logic behind it either, but this produces the output you give

``````data want;
set have(sortedby=id);
by id;
retain firstval countch;

if first.id then do;
firstval=num;
countch=0;
end;

if num=firstval then no_of_times=1;
else do;
countch=countch+1;
no_of_times=countch;
end;

drop firstval countch;
run;``````

All Replies
Super User
Posts: 9,599

## Re: Finding number of times changing id

Sorry, your logic doesn't seem right.  The second "1" is given 1, however the second "2" is given 2.  Either 1 should be 2, or 2 should be 1:

id number no_of_times
1 1 1
1 2 1
1 1 1   <- why is this 1, 1 already appears
1 2 2   <- and this is 2, as 2 already appears.

Anyways, something on the line of:

```data want;
set have;
retain count;
if num ne lag(num) then count=1;
else count=count+1;
run;```

Should work.

Contributor
Posts: 35

## Re: Finding number of times changing id

num column:

1 to 2 so chnage is 1

2 to 1 so chnage is no so i need 1 only

1 to 2 so change is 2

if again 1 to 2 comes change is 3

if again 2 to 1 then change is again 1

hope you understood

Contributor
Posts: 35

## Re: Finding number of times changing i

num column:

1 to 2 so chnage is 1

2 to 1 so chnage is no so i need 1 only

1 to 2 so change is 2

if again 1 to 2 comes change is 3

if again 2 to 1 then change is again 1

hope you understood

please find the id 2 also. may be it helps;

2 1 1
2 15 1
2 1 1
2 1 1
2 1 1
2 15 2
2 1 1
2 15 3

thank you.

Solution
‎08-01-2016 05:22 PM
Super Contributor
Posts: 340

## Re: Finding number of times changing id

Hello,

Not sure I understand the logic behind it either, but this produces the output you give

``````data want;
set have(sortedby=id);
by id;
retain firstval countch;

if first.id then do;
firstval=num;
countch=0;
end;

if num=firstval then no_of_times=1;
else do;
countch=countch+1;
no_of_times=countch;
end;

drop firstval countch;
run;``````
Contributor
Posts: 35

## Re: Finding number of times changing id

I will try your solution now.

My logic here is

here its like back anfd forth. 1 is back and 2 is forth

we need to count only forth ,how many times its going to forth.

each id:

so when it is first time changing 1 to 2 means no of times 1 for 1 and 2 for 1

if it goes to back(1)1 that means no change but value is 1
if it goes again 1 to 2 that mean second times it is going to forth(2) so value 1 for 1 and  2 for 2

if it goes to back(1)1 that means no change but value is 1

if it goes again 1 to 2 that mean second times it is going to forth(2) so value 1 for 1 and 2 for 3

hope it make sense.

Thank you

Contributor
Posts: 35

## Re: Finding number of times changing id

Hi Gamotte,

It worked.

Thank you so much

Contributor
Posts: 35

## Re: Finding number of times changing id

Hi Gamotte,

It worked.

Thank you so much

☑ This topic is solved.