topic Filter the last rows in SAS Enterprise Guide

Filter the last rows

usuario_estudo — Tue, 16 Oct 2018 17:34:43 GMT

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!

Re: Filter the last rows

novinosrin — Tue, 16 Oct 2018 17:42:07 GMT


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;

Re: Filter the last rows

usuario_estudo — Tue, 16 Oct 2018 18:07:18 GMT

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

Re: Filter the last rows

novinosrin — Tue, 16 Oct 2018 18:11:05 GMT

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

Re: Filter the last rows

usuario_estudo — Tue, 16 Oct 2018 18:15:44 GMT

Ok, I did that.

Thank you very much!

Re: Filter the last rows

novinosrin — Tue, 16 Oct 2018 18:24:00 GMT

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;

Re: Filter the last rows

novinosrin — Tue, 16 Oct 2018 18:25:35 GMT

I did a small edit to include call missing, my apologies for not reading the question/dataset earlier. See if the above works. Thanks!

Re: Filter the last rows

usuario_estudo — Tue, 16 Oct 2018 18:39:51 GMT

Yes, it's perfect, thank you!!!