Dear SAS community-
I would like to create new columns based on existing values in the original columns. I have the part of the code figured out where I can create new column names based on the original columns using a combination of arrays and the vname function. However, I cannot figure out how to specify that I only want the column name if there is a non-missing value in the original column. "Have" is my starting dataset. "Want" is the dataset I would like. Below those two is my current code. Any suggestions?
Thanks!
data have;
input SubID $ apple pear orange;
datalines;
AA 1 . 1
BB 1 2 8
CC 3 . .
DD . 4 3
;;
final dataset;
SubjectID fruit1 fruit2 fruit3
AA apple . orange
BB apple pear orange
CC apple . .
DD . pear orange
data want;
set have;
array new_fruit(3) $25 fruit1 - fruit3 ;
retain fruit1 - fruit3;
array old_fruit(3) apple pear orange ;
do i = 1 to 3;
if old_fruit(i) ^=. then do; *This part is not right though I can't figure out how to revise;
new_fruit(i) = vname(old_fruit(i));
end;
end;
drop i ;
run ;
Remove the RETAIN statement and all should be well.
Your IF THEN statement works, but doesn't erase the retained value from the previous observation.
Remove the RETAIN statement and all should be well.
Your IF THEN statement works, but doesn't erase the retained value from the previous observation.
I don't understand. A dataset is a rectangle so every observation has to include every variable. But the value can be missing.
Normally the easiest way to create variable names from data is to use PROC TRANSPOSE.
proc transpose data=have out=step1;
by subid;
run;
proc transpose data=step1 out=want(drop=_name_ _label_) prefix=fruit;
where not missing(col1);
by subid ;
var _name_;
run;
Sub Obs ID fruit1 fruit2 fruit3 1 AA apple orange 2 BB apple pear orange 3 CC apple 4 DD pear orange
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.