DATA Step, Macro, Functions and more

Finding number of times changing id

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

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
Regular Contributor
Posts: 234

Re: Finding number of times changing id

Posted in reply to ramchinna24

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,979

Re: Finding number of times changing id

Posted in reply to ramchinna24

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

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

Re: Finding number of times changing id

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
Regular Contributor
Posts: 234

Re: Finding number of times changing id

Posted in reply to ramchinna24

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

Re: Finding number of times changing id

Hi Gamotte. thanks for reply,

 

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

Re: Finding number of times changing id

Hi Gamotte,

 

It worked.

 

Thank you so much

Contributor
Posts: 27

Re: Finding number of times changing id

 

 

Hi Gamotte,

 

It worked.

 

Thank you so much

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 370 views
  • 3 likes
  • 3 in conversation