Hi,
I need to make a filter keeping only all the last rows marking "Flag=1".
Reading from bottom to top, I want to keep only the results until I get a value other than 1 for the variable "Flag".
Here's an example in the table below.
Input table
ID | Flag | Sub |
1000 | 2 | NA |
1000 | 1 | AB |
1000 | 9 | UH |
1000 | 1 | IF |
1000 | 5 | GE |
1000 | 1 | IP |
1000 | 1 | AB |
1000 | 1 | EH |
1000 | 1 | IA |
Output table
ID | Flag | Sub |
1000 | 1 | IP |
1000 | 1 | AB |
1000 | 1 | EH |
1000 | 1 | IA |
I need to have a SAS code that does this, could anyone help me?
Thank you very much!
data have;
input ID Flag Sub $;
cards;
1000 2 NA
1000 1 AB
1000 9 UH
1000 1 IF
1000 5 GE
1000 1 IP
1000 1 AB
1000 1 EH
1000 1 IA
2000 5 NA
2000 6 AB
2000 7 UH
2000 1 IF
2000 10 GE
2000 1 ES
2000 1 SW
2000 1 GF
2000 1 DY
2000 1 ZX
;
data want;
call missing(c);
do until(last.id);
set have;
by id flag notsorted;
if first.flag then c+1;
end;
call missing(n);
do until(last.id);
set have;
by id flag notsorted;
if first.flag then n+1;
if n=c and flag=1 then output;
end;
drop n c;
run;
data have;
input ID Flag Sub $;
cards;
1000 2 NA
1000 1 AB
1000 9 UH
1000 1 IF
1000 5 GE
1000 1 IP
1000 1 AB
1000 1 EH
1000 1 IA
;
data t;
do n=nobs to 1 by -1;
set have point=n nobs=nobs;
_n=n;
if flag=1 then output;
else stop;
end;
run;
proc sort data=t out=want(drop=_n);
by _n;
run;
Thank you very much, that was exactly it.
Could you tell me how to change the code if I want to do the same for each different IDs?
Example:
Input table
ID | Flag | Sub |
1000 | 2 | NA |
1000 | 1 | AB |
1000 | 9 | UH |
1000 | 1 | IF |
1000 | 5 | GE |
1000 | 1 | IP |
1000 | 1 | AB |
1000 | 1 | EH |
1000 | 1 | IA |
2000 | 5 | NA |
2000 | 6 | AB |
2000 | 7 | UH |
2000 | 1 | IF |
2000 | 10 | GE |
2000 | 1 | ES |
2000 | 1 | SW |
2000 | 1 | GF |
2000 | 1 | DY |
2000 | 1 | ZX |
Output table
ID | Flag | Sub |
1000 | 1 | IP |
1000 | 1 | AB |
1000 | 1 | EH |
1000 | 1 | IA |
2000 | 1 | ES |
2000 | 1 | SW |
2000 | 1 | GF |
2000 | 1 | DY |
2000 | 1 | ZX |
Sorry my bad, i didn;t see ID earlier. brb
Also @usuario_estudo Kindly revert the question back to open i.e not accepted coz it was my mistake
Ok, I did that.
Thank you very much!
data have;
input ID Flag Sub $;
cards;
1000 2 NA
1000 1 AB
1000 9 UH
1000 1 IF
1000 5 GE
1000 1 IP
1000 1 AB
1000 1 EH
1000 1 IA
2000 5 NA
2000 6 AB
2000 7 UH
2000 1 IF
2000 10 GE
2000 1 ES
2000 1 SW
2000 1 GF
2000 1 DY
2000 1 ZX
;
data want;
call missing(c);
do until(last.id);
set have;
by id flag notsorted;
if first.flag then c+1;
end;
call missing(n);
do until(last.id);
set have;
by id flag notsorted;
if first.flag then n+1;
if n=c and flag=1 then output;
end;
drop n c;
run;
I did a small edit to include call missing, my apologies for not reading the question/dataset earlier. See if the above works. Thanks!
Yes, it's perfect, thank you!!!
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!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.