topic Re: change values in Score1-Score12 aftewr first Score that equal 11 in SAS Programming

change values in Score1-Score12 aftewr first Score that equal 11

Ronein — Thu, 05 Mar 2020 10:03:11 GMT

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

Re: change values in Score1-Score12 aftewr first Score that equal 11

ed_sas_member — Thu, 05 Mar 2020 10:14:12 GMT

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;

Re: change values in Score1-Score12 aftewr first Score that equal 11

andreas_lds — Thu, 05 Mar 2020 10:41:30 GMT

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

Re: change values in Score1-Score12 aftewr first Score that equal 11

ballardw — Thu, 05 Mar 2020 17:31:46 GMT

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.