Create variable if value is missing

[AZIQ1]

Create new variable based on if statement

Options

2 seconds ago

Hi,

I want to create a new variable (newvar) based on conditional logic see below - i know this would be easy but I need help.

have:

var1  var2   var3   var11  var12  var13  

1           1      0          a       b                

1           1      1          b       b      c     

0           1       1         d       b      c    

1           0       0         a       b      c               

             0       2                 a        c               

want:

var1  var2   var3   var11  var12  var13  newvar

1           1      0          a       b              1      1    

1            1      1         b       b      c      0       0

0           1       1         d       b      c      0       0

1           0       0         a       b      c      1       1    

           0       2                  a      c       1       2

(if var11 ='a" but if var11 is blank then look for the value of 'a' in the next var12, or var 13 and if matches 'a' then  newvar= var1 else if var13 matches 'a'  and var11 is blank then new var should have the value of var3)   

I hope it makes sense 🙂

thanks

[AZIQ1]

Hi,

I want to create a new variable (newvar) based on conditional logic see below - i know this would be easy but I need help.

have:

var1  var2   var3   var11  var12  var13  

1           1      0          a       b                

1           1      1          b       b      c     

0           1       1         d       b      c    

1           0       0         a       b      c               

             0       2                 a        c               

want:

var1  var2   var3   var11  var12  var13  newvar

1           1      0          a       b              1      1    

1            1      1         b       b      c      0       0

0           1       1         d       b      c      0       0

1           0       0         a       b      c      1       1    

           0       2                  a      c       1       2

(if var11 ='a" but if var11 is blank then look for the value of 'a' in the next var12, or var 13 and if matches 'a' then  newvar= var1 else if var13 matches 'a'  and var11 is blank then new var should have the value of var3)   

I hope it makes sense 🙂

thanks

[skg74_mail_umkc_edu]

Could you Re frame part of this Question?
"(if var11 ='a" but if var11 is blank then look for the value of 'a' in the next var12, or var 13 and if matches 'a' then newvar= var1 else if var13 matches 'a' and var11 is blank then new var should have the value of var3) "

[AZIQ1]

var1 and var11    (if var11='a' then newvar  = var1) but if var11 is blank look for 'a' in  var12 

var2 and var12    (if var12='a' then newvar = var2)  but if both var11 or var12 ne 'a' or blank then look for 'a' in var13

var3 and var13    (if var13='a' then newvar = var3)

[ballardw]

This matches what I think your rules arebut I am assuming what to do when Var11 isn't blank or a and Var12 isn't blank or a and Var13 isn't a. Your problem has more than 3 paths through the options and you need to consider them a little clearer.

Also your example output is incorrect for the last line as Var12 is 'a' but you show a NewVar value of 2 but should be 0 from your rules.

data want;
   set have;
   select (var11);
      when ('a') NewVar=Var1;
      when ('') do;
                  select (var12);
                     when ('a') NewVar=Var2;
                     when ('')  If Var13='a' then NewVar=Var3;
                     otherwise NewVar=0; /* rule not described for var11='', Var12 not blank or 'a'*/
                  end;
                end;
      otherwise do;
                  select (var12);
                     when ('a') NewVar=Var2;
                     when ('')  If Var13='a' then NewVar=Var3;
                     otherwise NewVar=0; /* rule not described for var11 both Var12 not blank or 'a'*/
                  end;
                end;
                
   end;
run;

[AZIQ1]

Thank you

[skg74_mail_umkc_edu · Accepted solution]

Try this-

if var11 ^=" " and var11="a" then newvar=var11;

if newvar=" " and  var22 ^=" " and var22="a" then newvar=var22;

if newvar=" " and var33 ^=" " and var33="a" then newvar=var33;

Best Regards,

Sri.

[AZIQ1]

Thank you it worked

[ballardw]

You are missing what to do if the 'a' is in Var12.

It also helps to say what to do if var11 is not blank but is not 'a' AND what to do when var11 ='a'.

[Astounding]

It's a little hard to tell, but I think this is what you want.

data want;

set have;

newvar=0;
if var11='a' then newvar=var1;

else if var11 ne ' ' then return;

if var12='a' then newvar=var2;

else if var12 ne ' ' then return;

if var13='a' then newvar=var3;

run;

***** EDITED to change = to ne

It's untested, so let me know if you encounter any problems with it.

[AZIQ1]

Thank you