BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ramchinna24
Obsidian | Level 7

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
;

 

1 ACCEPTED SOLUTION

Accepted Solutions
gamotte
Rhodochrosite | Level 12

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

7 REPLIES 7
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

ramchinna24
Obsidian | Level 7

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

ramchinna24
Obsidian | Level 7

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.

gamotte
Rhodochrosite | Level 12

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;
ramchinna24
Obsidian | Level 7

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

ramchinna24
Obsidian | Level 7

Hi Gamotte,

 

It worked.

 

Thank you so much

ramchinna24
Obsidian | Level 7

 

 

Hi Gamotte,

 

It worked.

 

Thank you so much

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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