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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.