DATA Step, Macro, Functions and more

Flag set to 1

Reply
Super Contributor
Posts: 717

Flag set to 1

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.

Super User
Posts: 23,987

Re: Flag set to 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.

Super User
Posts: 6,927

Re: Flag set to 1

[ Edited ]

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;

Super Contributor
Posts: 717

Re: Flag set to 1

Posted in reply to Astounding

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

Super User
Posts: 23,987

Re: Flag set to 1

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


 

Super Contributor
Posts: 717

Re: Flag set to 1

subj term flag
1001
1001 lost 1
1001
1002 lost 1
1002 screen 1
1002
1003
1003
1003 random 1
1004 lost 1

Super Contributor
Super Contributor
Posts: 269

Re: Flag set to 1

So your flag is any record with a nonnull value of TERM?

 

 

 

Super Contributor
Posts: 717

Re: Flag set to 1

yes

Super User
Posts: 23,987

Re: Flag set to 1

if not missing(term) then flag=1;

Super Contributor
Super Contributor
Posts: 269

Re: Flag set to 1

Might not even need a flag.

 

Data want;

   set have;

   if not missing(term);

 

   code here

 

run;

 

Super User
Posts: 10,849

Re: Flag set to 1

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;
Ask a Question
Discussion stats
  • 10 replies
  • 247 views
  • 1 like
  • 5 in conversation