topic Assign values to new variable only if source variable missing in SAS Programming

Assign values to new variable only if source variable missing

JMagenta — Fri, 13 Jan 2023 23:46:14 GMT

Hello and Thank you!

I wanted to see if could create a new variable with values of a another variable and values from a range. I only want to replace missing values, and without duplicates.
For instance my code looks like this:

data have;
infile datalines dlm=',';
input blah $ var1 var2 ;
datalines;
A, 1, 2,
b, 3, 2,
c, 3, .,
d, 8, .,
e, 5, .,
f, 7, 2,
g, 3, 1,
h, 9, 4,
i, 8, .
;
run;

data want;
set have;
If var2 ne . then var3=var2;
else if missing(var2) then var3=rand("integer", 1800, 1803);
run;

What I want is this :

> What I get is this:

How can I end this pain? I know I need a do loop of some sort and/or an array maybe. How should I set it up? I have a much longer dataset and I don't want to assign duplicate values to this new variable. I know the answe

Thank you!!!

Re: Assign values to new variable only if source variable missing

Patrick — Sat, 14 Jan 2023 02:44:05 GMT

Welcome to the SAS communities.

Below two options how you could do this.

data want(drop=_cnt);
  set have;

  if var2 ne . then var3=var2;
  else var3=1800+_n_;

  if var2 ne .  then var4=var2;
  else 
    do;
      _cnt+1;
      var4=1800+_cnt;
    end;
run;

Using a random function like in your code will create random values which then obviously won't be sequential AND which also could repeat.

Option 1 in the code I've posted uses automatic variable _n_ which contains the observation number and though is sequential and unique per row.

Option 2 creates counter variable _cnt. Using SAS syntax _cnt+1; on the left side of the equal sign is "specialty" syntax to create and populate a variable that will retain values. This syntax is often used for any sort of counter variables than need to increase over multiple observations.

Re: Assign values to new variable only if source variable missing

s_lassen — Sat, 14 Jan 2023 10:50:50 GMT

If you just want the values to be unique, here is a simple solution:

data want;
  set have;
  var3=coalesce(var2,1800+_N_);
run;

However, if you want to increase the value filled in only when it is actually used, it goes like this:

data want;
  set have;
  retain _new 1800;
  if missing(var2) then do;
    _new+1;
    var3=_new;
    end;
  else
    var3=var2;
  drop _new;
run;

Re: Assign values to new variable only if source variable missing

JMagenta — Tue, 17 Jan 2023 16:22:54 GMT

Worked like a charm, thank you!