topic Re: Selecting first non-missing value for each participant ID in SAS Programming

Selecting first non-missing value for each participant ID

karns — Wed, 09 Jul 2014 15:23:59 GMT

I have a data set containing many participants. Each participant has multiple events. For each participant and each event, I want a list of the variable value. This might be best described by demonstration:

For example,

PID EVENT VAR

1392 1 .

1392 1 13

1392 2 12

2456 1 3

2456 1 .

2456 2 .

I want to end up with a list like this:

PID EVENT VAR

1392 1 13

1392 2 12

2456 1 3

2456 2 .

Is there a particular method for doing this? Participants have more than one EVENT. And within each EVENT, VAR is the same (but some observations are .) In some cases, VAR is . for the entire event. In other cases, VAR is . for only a few cases within an EVENT.

Re: Selecting first non-missing value for each participant ID

Reeza — Wed, 09 Jul 2014 15:35:20 GMT

Assuming you can't have records with all missing, then remove missing at the start and then keep only the first occurrence.

proc sort data=have;

by pid event;

run;

data want;

set have;

where var ne .;

by pid event;

if first.event;

run;

Re: Selecting first non-missing value for each participant ID

stat_sas — Wed, 09 Jul 2014 15:42:18 GMT

data want;
set have;
if event=1 and var=. then delete;
run;

proc sql;
select distinct pid,event,var
from want;
quit;

Re: Selecting first non-missing value for each participant ID

eirhpaul — Wed, 09 Jul 2014 15:45:03 GMT

Let's call your first data example ds1 to get ds2 (second data example) try the following:

data ds2 (drop = issetflg);

retain issetflg;

set ds1;

by pid event;

if first.event then issetflg = 'N';

if var ne . then do;

issetflg = 'Y';

output;

end;

if last.event then

if issetflg = 'N' then

output;

run;

Re: Selecting first non-missing value for each participant ID

FloydNevseta — Wed, 09 Jul 2014 15:49:12 GMT

Assuming the data is already sorted by pid and event, try this:

data want;

set have;

by pid event;

retain found;

if first.event then found = 0;

if found = 0 and ( not missing( var ) or last.event ) then do;

output;

found = 1;

end;

run;

Re: Selecting first non-missing value for each participant ID

slchen — Wed, 09 Jul 2014 16:00:27 GMT

proc sql;
select distinct pid,event,var from have group by pid,event having var=max(var);
quit;

Re: Selecting first non-missing value for each participant ID

FloydNevseta — Wed, 09 Jul 2014 16:01:09 GMT

Here's another approach:

data want;

set have (rename=(var=varx));

by pid event;

retain var;

if first.event then var = .;

var = coalesce( var, varx );

if last.event then output;

drop varx;

run;