BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

Hello

I have a raw data table that contain about each customer information about score in follow up period (12 months).

score 11 is defined as a failure.

I want to change the values in fields Score1-Score12 by following rule:

IF one of the scores get value 11 then all scores after will have null value.

For example:

For ID 11111111 the first score that equal 11 is Score 4  so Score5-Score12 will get null value

For ID 22222222   there is no score with value 11 so there are no changes in values of score1-score12

For ID 33333333 the first score that equal 11 is Score 1  so Score2-Score12 will get null value

For ID 44444444   there is no score with value 11 so there are no changes in values of score1-score12

3 REPLIES 3
ed_sas_member
Meteorite | Level 14

Hi @Ronein 

 

Here is an attempt to achieve this. Does it meets your expectations?

data have;
	input ID score1-score12;
	datalines;
11111111 1 1 1 11 1 1 1 1 1 1 1 1
22222222 1 1 1 1 1 1 1 1 1 1 1 1
33333333 11 1 1 1 1 1 1 1 1 1 1 1 
44444444 1 1 1 1 1 1 1 1 1 1 1 1
;
run;

data want;
	set have;
	array _score(*) score1-score12;
	do i=1 to dim(_score);
		if _score(i) = 11 then flag = i+1;
	end;
	if flag=. then flag = (dim(_score)+1);
	do j=flag to dim(_score);
		_score(j) = .;
	end;
	drop i j flag;
run;

Capture d’écran 2020-03-05 à 11.13.34.png 

andreas_lds
Jade | Level 19

or with just one loop

 

data wantB;
   set have;

   array scores score1-score12;

   kill = 0;

   do i = 1 to dim(scores);
      if kill then scores[i] = .;
      if not kill and scores[i] = 11 then kill = 1;
   end;

   drop i kill;
run;

thanks for providing data @ed_sas_member

ballardw
Super User

And yet another:

data want;
   set have;
   array scores score1-score12;

   if 0< whichn(11, of scores(*)) < 11 then do i=(whichn(11, of scores(*))+1) to dim(scores);
      scores[i]= .;
   end;
   drop i;
run;

The WHICHN, and WHICHC for character varaibles, will return the position that the value of the first parameter, the 11 in this case, is first found in left to right order in the list of following values, here using the array notation. The order of variables assigned to the array would be the order evaluated. It returns 0 if the value is not found.

 

You didn't really explicitly say what might happen if the LAST value is 11. I am assuming that you do nothing.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 3 replies
  • 365 views
  • 2 likes
  • 4 in conversation