New user ask for help: How build a new variable by select one from four variables?

[jing2000yr]

Hi,

I am a new user ask for help. Attached the sample data, I have four variables from 4 visits which acutually measured the same biological marker. Subjects were measured in one or two visits but not all, so the Var 1-4 had missing values. I want to build a new variable, by selecting one from the four variables.

1. How to select from the first available data in the four visits to build the new one?

2. How to select the last available data in the four visits to build the new one?

Thanks!

Jing

[Kurt_Bremser]

You seem to have an Excel issue. You posted a screenshot of an Excel spreadsheet, and attached an Excel file.

I recommend that you post your question at an Excel-oriented forum, this here are the SAS communities.

[antonbcristina]

Considering you might want to solve this in SAS, the following will work. You want to use an array to iterate through the available measures (var1-var4). You will assign first to be the first non-missing value and assign last to be the last non-missing value.

data want;
    set have;
    array visits [*] var1-var4;
    do i = 1 to dim(visits);
        if first=. then first=visits[i];       
        if visits[i]^=. then last=visits[i];
    end;
run;

[Tom]

Use the COALESCE() function to select the first non-missing value from a list.  Since your variable names have numeric suffixes you can use a variable list.

first = coalesce(of var1-var4);

Otherwise just list the variable names:

first = coalesce(of varA varB varC varD);

To find the last non-missing value just list the variables in the opposite order.

last = coalesce(of var4-var1);