Creating new variable from array

[aivoryuk]

Hi I would like to create a new variable based on the result of a array. so it will loop through 10 variables and if the variables match where the variable is like 'RR' then the new variable will show a 1 or a zero.

This is the code I have so far.

data year2012;
set exphandsql;
if total_turnover = . then total_turnover = 0;

array R_indicator(10) Reason_Code_1-Reason_Code_10;

  do i = 1 to 10 ;
     if R_indicator =:'RR' then <new variable>= '1' ;
else <new variable>='0';
  end;
  drop i;

run;

It doesn't seem to working properly.

Regards

[Vince28_Statcan]

Hi Aivoryuk,

There are potentially 2 errors. First, if Reason_Code_i variables are not of length 2, then R_indicator would actually be "RR      " aka white space padding up to the length of the variable. Use trim function to fix this

if trim(R_indicator)='RR' then newvar='1';

The second error is a logical one. Since you loop on all 10 codes and you have an else stement, the else statement will apply every step of the loop where the value isn't RR. Thus, unless trim(reason_code_10)='RR' you will always see 0.

To fix this, you can either remove it all together if you don't mind missing values instead of 0 or remove the else statement and add a if statement outside the loop. That is, something like

...

end;

if newvar=. then newvar=0;

Hope this helps

Vincent

[Jaheuk]

when there is a RR in reason_code_10 in will work, else it will always be 0

test this:

data year2012;
set sashelp.class;

array  R_indicator(2) $ name sex;
array  Flag(2) ;

  do i = 1 to 2 ;
     if R_indicator =: 'M' then flag=1 ;
        else flag=.;
  end;

  test=nmiss(of flag(*)) ;

  drop i ;
run;

when nothing is found all flag are missing

JAHEUK

[Haikuo · Accepted solution]

My primary concern is how you construct your "new variable". Is it one "new variable" for each different "Reason_Code", or just ONE "new variable" for all of them, and if either of "Reason_Code" starts with 'RR', the "new variable" will be 1, otherwise will be 0?

If it is the former, I think has offered an answer; if it is the latter, try this:

data year2012;
set exphandsql;
if total_turnover = . then total_turnover = 0;

array R_indicator(10) Reason_Code_1-Reason_Code_10;

new_variable='0';

  do i = 1 to 10 ;
     new_variable=ifn(R_indicator =:'RR', '1', new_variable) ;

  end;
  drop i;

run;

Or more efficiently:

new_variable='0';

  do i = 1 to 10 ;
     if R_indicator =:'RR' then do; new_variable= '1'; leave;end;

  end;

Haikuo

[Keith]

@hai.kuo Am I going blind or did you add the last bit with the LEAVE statement in your original post, or post it in an edit?  I almost posted that exact solution, but decided to use UNTIL instead.

[Haikuo]

LOL, Keith. I updated it within 1 minute, figured there should be no difference, guess I was wrong.

[Keith]

I'll cancel that call with the opticians then...

[Keith]

Your new_variable is being reset at every iteration of the loop, so it will only show a value of 1 if Reason_Code_10 begins with 'RR'.

The solution from solves this problem, another option is to put an UNTIL statement in the do loop so that it stops processing when 'RR' is encountered

     do i=1 to 10 until (R_indicator{i}=:'RR');

Yet another possibility, if the letters 'RR' only occur at the start of the string (or you want to search for them anywhere in the string), is to drop the array completely and use the INDEX and CATX functions together.

     new_variable=index(catx(',',of Reason_Code:),'RR')>0;

[Haikuo]

Inspired by 's second suggestion, here is one approach that only counts the starting 'RR',

new_variable=prxmatch('/\bRR/',catx(' ',of Reason_Code:))>0;

Haikuo

[aivoryuk]

Thanks guys that really helped as well helping my understanding