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

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;

View solution in original post


All Replies
Super User
Super User
Posts: 7,430

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

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: 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
  • 355 views
  • 3 likes
  • 3 in conversation