CODEWITHSUNDEEP
sas
morgan121
morgan121

Reputation: 2253

Create a new, named column based on a formatted numeric variable

I have a dataset (DIN) that consists of formatted numeric variables (e.g., column 1 'BLD' has values 1-3, but they are formatted as 'Yes', 'No', 'Unknown'). All columns have slightly different formatting.

In each row, only one column has a value, the rest are missing. I am trying to use the following to get the maximum of each row (which will always be the non-missing value)

data DIN;
    set DIN;
    MAX = max(of BLD--VASC);
run;

Unfortunately as these columns are numeric the MAX column is showing as numbers, not the formatted value. I have tried using vvalue to get the formatted value, like below but I don't know how to do this for all columns at once.

data _null_;
    set DIN;
    BLD_C = vvalue(BLD);
run;

I felt like a do loop might help, and I tried looping over an array of variable names, but it just doesn't work. Nothing seems to happen

data DIN_C;
    set DIN;
    array nums(*) _numeric_;

    do i = 1 to dim(nums);
        nums_C = vvalue(nums(i));
    end;
run;

Can anyone help me? Or is there another approach I could take for this problem?

Upvotes: 0

Views: 264

Answers (1)

Tom
Tom

Reputation: 51566

You can use MAX() to find the actual non-missing numeric value. Then use WHICH() to find the index number of the variable with that value. Now you can use VVALUE() to find the formatted value of that variable.

data DIN_FIXED;
  set DIN;
  array _num BLD--VASC
  length max 8 max_formatted $50 ;
  MAX = max(of _num[*]);
  if not missing(max) then max_formatted=vvalue(_num[which(max,of _num[*])]);
run;

Upvotes: 2

Related Questions