subjectid term
1001
1001
1001 lost
1002 screen
1002 random
1002
1003 screen
1003 random
1003 lost
how to set flag to 1 for the below condition?
if a subject has multiple records and only one record is not null then flag should set to 1
in this case 1001 can have flag 1.
Run a proc freq or means on the data by ID to get the number of missing. Merge data back in and create flag.
Since you didn't give all the details about when FLAG should be 1 (all 1001 observations, just the non-null one, just the last one?), I'll do it in the easiest possible way. FLAG will be 1 on the last observation for a SubjectID, if there is only one non-null value.
This assumes your data set is already in order by SubjectID:
data want;
set have;
by subjectID;
if first.subjectID then non_null=0;
if term > ' ' then non_null + 1;
if last.subjectID=1 and first.subjectID=0 and non_null = 1 then flag=1;
drop non_null;
run;
****************************************
EDITED: Revised so the non-null value gets flagged (not as easy, of course):
data want;
n_recs=0;
non_null=0;
do until (last.subjectID);
set have;
by subjectID;
n_recs + 1;
if term > ' ' then non_null + 1;
end;
do until (last.subjectID);
set have;
by subjectID;
if term > ' ' and non_null=1 and n_recs > 1 then flag=1;
else flag=.;
output;
end;
drop n_recs non_null;
run;
not just the last record, the non null value.
if 1001 has a value in the second record and first and last records are null, then the non null record has to have a flag
Show it.
@SASPhile wrote:
not just the last record, the non null value.
if 1001 has a value in the second record and first and last records are null, then the non null record has to have a flag
subj term flag
1001
1001 lost 1
1001
1002 lost 1
1002 screen 1
1002
1003
1003
1003 random 1
1004 lost 1
So your flag is any record with a nonnull value of TERM?
yes
if not missing(term) then flag=1;
Might not even need a flag.
Data want;
set have;
if not missing(term);
code here
run;
data have;
infile cards truncover;
input subjectid term $;
cards;
1001
1001
1001 lost
1002 screen
1002 random
1002
1003 screen
1003 random
1003 lost
;
run;
proc sql;
select *,n(term)=1 as flag
from have
group by subjectid;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.