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.
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;
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;
should it be if first.subject then flag_remove=0; ??
But in this case i need to check if stop date is present for only 1B and then delete.
Do you need to remove ALL the observations for that subject, or just the 1B observations?
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.
@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.
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;
i get NOTE: Variable last.id is uninitialized.
@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.
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
?
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
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;
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.