topic Re: Creating new variable from array in SAS Procedures

Creating new variable from array

aivoryuk — Tue, 23 Jul 2013 12:13:20 GMT

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

Re: Creating new variable from array

Vince28_Statcan — Tue, 23 Jul 2013 12:35:51 GMT

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

Re: Creating new variable from array

Jaheuk — Tue, 23 Jul 2013 12:41:01 GMT

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

Re: Creating new variable from array

Haikuo — Tue, 23 Jul 2013 12:57:28 GMT

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

Re: Creating new variable from array

Keith — Tue, 23 Jul 2013 13:19:32 GMT

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;

Re: Creating new variable from array

Keith — Tue, 23 Jul 2013 13:24:36 GMT

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

Re: Creating new variable from array

Haikuo — Tue, 23 Jul 2013 13:29:40 GMT

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

Re: Creating new variable from array

Keith — Tue, 23 Jul 2013 13:36:21 GMT

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

Re: Creating new variable from array

Haikuo — Tue, 23 Jul 2013 14:09:19 GMT

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

Re: Creating new variable from array

aivoryuk — Tue, 23 Jul 2013 14:35:39 GMT

Thanks guys that really helped as well helping my understanding