BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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.

10 REPLIES 10
Reeza
Super User

Run a proc freq or means on the data by ID to get the number of missing. Merge data back in and create flag.

Astounding
PROC Star

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;

SASPhile
Quartz | Level 8

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

Reeza
Super User

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


 

SASPhile
Quartz | Level 8

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

HB
Barite | Level 11 HB
Barite | Level 11

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

 

 

 

SASPhile
Quartz | Level 8

yes

Reeza
Super User

if not missing(term) then flag=1;

HB
Barite | Level 11 HB
Barite | Level 11

Might not even need a flag.

 

Data want;

   set have;

   if not missing(term);

 

   code here

 

run;

 

Ksharp
Super User
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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 1103 views
  • 1 like
  • 5 in conversation