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
;
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;
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.
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
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.
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;
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
Hi Gamotte,
It worked.
Thank you so much
Hi Gamotte,
It worked.
Thank you so much
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.