Creating a New Variable Based on the Lowest Value of Multiple Existing Variables

[jpeitz]

I have a cross-sectional data set with multiple 'age at diagnosis' variables. i.e. : 

data have;
input seqn cxage1 cxage2 cxage3 ;
1 58 51 .
2 24 35 55 
3 30 . .
4 52 44 .
run;

(I didn't manually input the data so this code might be wrong, it's just to show about what the set would look like if it was entered this way)

I would like to create one variable that uses the lowest 'age at diagnosis' whether it be cxage1 cxage2 or cxage3 so that the new dataset would look like: 

data want;
input seqn cxage1 cxage2 cxage3 lowage;
1 58 51 . 51
2 24 35 55 24
3 30 . . 30
4 52 44 . 44
run;

I am a fairly novice user for SAS so I've been searching and I've tried a proc sql statement, do until statement, and first statement and all gave me back binary output which is not what I want. 

Really appreciate any help and responses

Thank you

jpeitz

[PaigeMiller · Accepted solution]

So, SQL is the wrong tool. (Yes, you could make it work in SQL, but in general SQL still the wrong tool to do statistics with; just because you can do something in SQL doesn't mean you should do that thing in SQL)

A much better approach is a DATA step, where you can use the MIN() function. Assuming the variable names all begin with cxage, this will work

data want;
    set have;
    lowage=min(of cxage:);
run;

[jpeitz]

This worked! Thank you so much!

[ballardw]

This is one way:

data want;
   set have;
   array c(*) cxage: ;
   lowage = min(of c(*));
run; 

The key is the call to the MIN function, minimum of a list of variables. You could list the names directly in the function instead of the array as : lowage = min(cxage1, cxage2, cxage3); The array definition using a shorthand list will use all variables that have names starting with cxage. So if your data ends up with 20 variables with that name then it will adjust to use all of them.

[jpeitz]

Thank you!