DATA Step, Macro, Functions and more

Create variable if value is missing

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

Create variable if value is missing

[ Edited ]
Create new variable based on if statement

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 Smiley Happy

thanks


Accepted Solutions
Solution
‎07-21-2016 11:53 AM
Occasional Contributor
Posts: 11

Re: Create variable if value is missing

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.

View solution in original post


All Replies
Occasional Contributor
Posts: 11

Re: Create variable if value is missing

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) "
Contributor
Posts: 64

Re: Create variable if value is missing

[ Edited ]

 

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)

 

 

 

Super User
Posts: 10,454

Re: Create variable if value is missing

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;
Contributor
Posts: 64

Re: Create variable if value is missing

Thank you
Solution
‎07-21-2016 11:53 AM
Occasional Contributor
Posts: 11

Re: Create variable if value is missing

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.

Contributor
Posts: 64

Re: Create variable if value is missing

Thank you it worked
Super User
Posts: 10,454

Re: Create variable if value is missing

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

 

Super User
Posts: 5,069

Re: Create variable if value is missing

[ Edited ]

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.

Contributor
Posts: 64

Re: Create variable if value is missing

Thank you
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 440 views
  • 2 likes
  • 4 in conversation