Array and Column Names

[sophia_SAS]

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 ;

[Astounding · Accepted solution]

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.

[sophia_SAS]

Thanks!

[Tom]

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