topic Re: New user ask for help: How build a new variable by select one from four variables? in New SAS User

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

jing2000yr — Tue, 10 Oct 2023 02:22:02 GMT

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

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

Kurt_Bremser — Tue, 10 Oct 2023 04:04:34 GMT

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.

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

antonbcristina — Tue, 10 Oct 2023 13:24:17 GMT

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;

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

Tom — Tue, 10 Oct 2023 13:34:34 GMT

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);