topic Re: Deleting some obs conditionally of the previous obs. in SAS Programming

Deleting some obs conditionally of the previous obs.

Hugo_B — Mon, 20 May 2019 17:54:00 GMT

Hello

I have a data set that consist of a column, 2 consecutive obs can have the same value just the second has a '/'


DATA HAVE;
INPUT COLUMN $200;
DATALINES;
AAAA
BBBB
/BBBB
CCCC
DDDD
EEEE
/EEEE
FFFF
/FFFF
GGGG
;
RUN;

I want to build a program that check if 2 consecutive rows have the same value and delete both.

To have :

DATA WANT;
INPUT COLUMN $200;
DATALINES;
AAAA
CCCC
DDDD
GGGG
;
RUN;

Do you have any idea how to do that ?

Re: Deleting some obs conditionally of the previous obs.

noling — Mon, 20 May 2019 18:13:00 GMT

I like the BY statement with first-DOT and last-DOT's. This doesn't delete records, but instead just doesn't output them if the values aren't unique. You can see where I remove the "/" character in the second dataset. This also assumes that the original data is sorted.

DATA HAVE;
length column $200;
INPUT COLUMN;
DATALINES;
AAAA
BBBB
/BBBB
CCCC
DDDD
EEEE
/EEEE
FFFF
/FFFF
GGGG
;;;;;
RUN;
data temp;
	set have;
	if substr(column,1,1)="/" then column=substr(column,2, 200);
run;
data want;
	set temp;
	by column;
	if first.column and last.column;
run;

Re: Deleting some obs conditionally of the previous obs.

novinosrin — Mon, 20 May 2019 18:20:28 GMT



DATA HAVE;
infile cards truncover;
INPUT COLUMN $200.;
DATALINES;
AAAA
BBBB
/BBBB
CCCC
DDDD
EEEE
/EEEE
FFFF
/FFFF
GGGG
;
RUN;

data want;
merge have have(firstobs=2 rename=COLUMN=C);
COLUMN=compress(COLUMN,'/');
C=compress(c,'/');
if column=c or lag(COLUMN)=COLUMN then delete;
drop c;
run;

Re: Deleting some obs conditionally of the previous obs.

novinosrin — Mon, 20 May 2019 18:39:05 GMT


DATA HAVE;
infile cards truncover;
INPUT COLUMN $200.;
DATALINES;
AAAA
BBBB
/BBBB
CCCC
DDDD
EEEE
/EEEE
FFFF
/FFFF
GGGG
;
RUN;

proc sql;
create table want(drop=c) as
select *,compress(COLUMN,'/') as c
from have
group by c
having count(c)=1;
quit;

Re: Deleting some obs conditionally of the previous obs.

Kurt_Bremser — Tue, 21 May 2019 07:53:12 GMT

data want;
merge
  have
  have (rename=(column=_column) firstobs=2)
;
if substr(column,1,1) ne '/' and column ne substr(_column,2);
drop _column;
run;

proc print data=want noobs;
run;

Result:

column

 AAAA 
 CCCC 
 DDDD 
 GGGG