I have a dataset in which some variables belonging to the same subject have repeat values. I want to keep only the first one for some variables. For other variables, I want to keep all of the values even if repeated. Here is a simplified version of the data, where I want to keep the first of Apples and Cherries, but all repeats of Bananas. (I had trouble getting this fake data to load properly so imagine that the '' cells are actually blank) data WORK.have;
infile datalines missover delimiter=',';
input ID 1 Visit $ Apples $ Bananas $ Cherries $;
datalines;
1,Visit1,Apples,Bananas,''
1,Visit2,Apples,Bananas,''
2,Visit1,'',Bananas,Cherries
2,Visit2,Apples,Bananas,''
2,Visit3,Apples,Bananas,Cherries
3,Visit1,Apples,Bananas,''
3,Visit3,'','',Cherries
4,Visit1,Apples,Bananas,''
4,Visit2,'','',Cherries
4,Visit3,'','',Cherries
5,Visit1,Apples,Bananas,''
6,Visit1,Apples,Bananas,''
; Here's what I want the dataset to look like data WORK.want;
infile datalines missover delimiter=',';
input ID 1 Visit $ Apples $ Bananas $ Cherries $;
datalines;
1,Visit1,Apples,Bananas,''
1,Visit2,'',Bananas,''
2,Visit1,'',Bananas,Cherries
2,Visit2,Apples,Bananas,''
2,Visit3,'',Bananas,''
3,Visit1,Apples,Bananas,''
3,Visit3,'','',Cherries
4,Visit1,Apples,Bananas,''
4,Visit2,'','',Cherries
4,Visit3,'','',''
5,Visit1,Apples,Bananas,''
6,Visit1,Apples,Bananas,''
; I tried code like this but it doesn't work because the first non-missing field is not always the first row data want;
set have;
array onetime {2} $ Apples Cherries;
by id;
do i=1 to 2;
if first.id then onetime(i)=onetime(i);
else call missing(of onetime(i));
end;
run; Appreciate your assistance! I am using SAS 9.4.
... View more
