topic Re: Finding number of times changing id in SAS Programming

Finding number of times changing id

ramchinna24 — Mon, 01 Aug 2016 11:37:01 GMT

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
;

Re: Finding number of times changing id

RW9 — Mon, 01 Aug 2016 11:49:01 GMT

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.

Re: Finding number of times changing id

ramchinna24 — Mon, 01 Aug 2016 11:55:28 GMT

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

Re: Finding number of times changing id

ramchinna24 — Mon, 01 Aug 2016 12:00:24 GMT

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.

Re: Finding number of times changing id

gamotte — Mon, 01 Aug 2016 12:26:47 GMT

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;

Re: Finding number of times changing id

ramchinna24 — Mon, 01 Aug 2016 20:45:41 GMT

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

Re: Finding number of times changing id

ramchinna24 — Mon, 01 Aug 2016 21:21:50 GMT

Hi Gamotte,

It worked.

Thank you so much

Re: Finding number of times changing id

ramchinna24 — Mon, 01 Aug 2016 21:22:23 GMT

Hi Gamotte,

It worked.

Thank you so much