topic Re: Create variable if value is missing in SAS Programming

Create variable if value is missing

AZIQ1 — Wed, 20 Jul 2016 19:04:35 GMT

Create new variable based on if statement

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

Re: Create variable if value is missing

skg74_mail_umkc_edu — Wed, 20 Jul 2016 19:33:26 GMT

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) "

Re: Create variable if value is missing

ballardw — Wed, 20 Jul 2016 19:42:19 GMT

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

Re: Create variable if value is missing

AZIQ1 — Wed, 20 Jul 2016 19:53:18 GMT

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)

Re: Create variable if value is missing

Astounding — Wed, 20 Jul 2016 20:23:20 GMT

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.

Re: Create variable if value is missing

ballardw — Wed, 20 Jul 2016 20:20:29 GMT

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;

Re: Create variable if value is missing

skg74_mail_umkc_edu — Wed, 20 Jul 2016 20:22:43 GMT

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.

Re: Create variable if value is missing

AZIQ1 — Thu, 21 Jul 2016 15:53:51 GMT

Thank you

Re: Create variable if value is missing

AZIQ1 — Thu, 21 Jul 2016 15:54:02 GMT

Thank you it worked

Re: Create variable if value is missing

AZIQ1 — Thu, 21 Jul 2016 15:54:24 GMT

Thank you