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
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;
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
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.
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.