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!!!
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;
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.
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;
Worked like a charm, thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.