BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
vraj1
Quartz | Level 8
subject id text start date time  STOP DATE
39 Only in 1A nature 09 Oct 2014 07:15  
39 Only in 1B beauty UN Mar 2015   10 Sep 2015
45 Only in 1A HUMAN 09 Oct 2014    

 

I need to remove subjects which has stop date in "Only in 1B" if the same subject has "Only in 1A" observation also.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
data delfile (keep=subject);
set have;
by subject;
retain delflag1 delflag2;
if first.subject
then do;
  delflag1 = 0;
  delflag2 = 0;
end;
if id = 'Only in 1A' then delflag1 = 1;
if id = 'Only in 1B' and stop_date ne . then delflag2 = 1;
if last.subject and delflag1 and delflag2 then output;
run;

data want;
merge
  have (in=a)
  delfile (in=b)
;
by subject;
if a and not b;
run;

View solution in original post

15 REPLIES 15
Shmuel
Garnet | Level 18

Assumed data is sorted by subject id:

 

data want;

  set have;

       by suject id;

            retain flag_remove;

           if first.id then flag_remove=0;

           if id = '1A' than flag_remove = 1;

           if id = '1B' and flag_remove=1 then delete;

run;

vraj1
Quartz | Level 8

should it be if first.subject then flag_remove=0; ??

vraj1
Quartz | Level 8

But in this case i need to check if stop date is present for only 1B and then delete.

Astounding
PROC Star

Do you need to remove ALL the observations for that subject, or just the 1B observations?

vraj1
Quartz | Level 8

If for the same subject i have both 1A and 1B and i have stop date for 1B  and missing stop date for 1A then only i need to remove 1B obs. else keep as it is.

Kurt_Bremser
Super User

@vraj1 wrote:

If for the same subject i have both 1A and 1B and i have stop date for 1B  and missing stop date for 1A then only i need to remove 1B obs. else keep as it is.


So you just moved the goalposts.

Kurt_Bremser
Super User
data delfile (keep=subject);
set have;
by subject;
retain delflag1 delflag2;
if first.subject
then do;
  delflag1 = 0;
  delflag2 = 0;
end;
if id = 'Only in 1A' then delflag1 = 1;
if id = 'Only in 1B' and stop_date ne . then delflag2 = 1;
if last.subject and delflag1 and delflag2 then output;
run;

data want;
merge
  have (in=a)
  delfile (in=b)
;
by subject;
if a and not b;
run;
vraj1
Quartz | Level 8

i get NOTE: Variable last.id is uninitialized.

Kurt_Bremser
Super User

@vraj1 wrote:

i get NOTE: Variable last.id is uninitialized.


Corrected my code from last.id to last.subject.

Happens when one has to work without test data.

vraj1
Quartz | Level 8
Thanks a lot Kurt
vraj1
Quartz | Level 8
Sorry for asking but in this case i am loosing those subjects whereas i need the subject with only 1A observation and remove 1B obs in the case of stop date present in 1B and not in 1A. Sorry for all the confusion
Kurt_Bremser
Super User

So, for a given subject, you want to remove the "1B" observation if

- the "1B" observation has a stop_date

- a previous "1A" observation had no stop_date

?

OTOH, the observation would stay when either

- no previous "1A" observation is present

- a previous "1A" observation does not have a stop_date

- the "1B" observation has no stop_date

?

vraj1
Quartz | Level 8

remove the "1B" observation if

- the "1B" observation has a stop_date

- a previous "1A" observation had no stop_date

 

If there is only one onservation for a subject with either 1A or 1B it stays as it is.

if there is stop date for 1A and stop date for 1B then both stays.

If there is no stop date for 1B then also it stays

Kurt_Bremser
Super User
data want;
set have;
by subject;
retain delflag;
if first.subject then delflag = 0;
if id = 'Only in 1A' and stop_date = . then delflag = 1;
if id = 'Only in 1B' and stop_date ne . and delflag then delete;
run;

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
  • 15 replies
  • 993 views
  • 1 like
  • 4 in conversation