Keep one empty line

[MM88]

Hi community,

Is there a way to keep one empty line if there are multiple contiguous lines? I want to maintain the same row order.

I tried distinct, but only one empty line is returned and the original row order is lost.

proc sql;
CREATE TABLE  HAVE
(var1 char(10),
 var2 char(10));

INSERT INTO HAVE
VALUES('A', 'B')
VALUES('', '')
VALUES('', '')
VALUES('', '')
VALUES('C', 'D')
VALUES('', '')
VALUES('', '')
VALUES('E', 'F')
VALUES('', '')
VALUES('', '')
VALUES('', '')
VALUES('', '')
VALUES('G', 'H')
;
QUIT;

proc sql;
CREATE TABLE  WANT
(var1 char(10),
 var2 char(10));

INSERT INTO WANT
VALUES('A', 'B')
VALUES('', '')
VALUES('C', 'D')
VALUES('', '')
VALUES('E', 'F')
VALUES('', '')
VALUES('G', 'H');
QUIT;

Thanks

[ballardw · Accepted solution]

Sequential processing is not an SQL strong point. If there aren't other variable values involved SQL is not likely to get anything resembling what you want. A SAS data set could but why do you want any "empty line" values at all?

data want;
   set have;
   retain bc;
   if not missing(var1) then bc=0;
   else bc+1;
   if bc<2;
   drop bc;
run;

BC retains a variable for counting blank lines. That means the value is kept across iterations of the data step. Each time var1 is encountered with a none missing value it is reset to 0. If var1 is missing then the count is incremented. The If BC<2 means only the BC=0 or 1 are written to the output set. The drop means the BC variable isn't written to the set.

[Tom]

One line of what?

You can use BY group processing.  Just use the NOTSORTED option to let SAS know that you do not expect the observations to actually be sorted.

data want;
  set have;
  by var1 var2 notsorted;
  if 2=cmiss(of var1 var2) and not first.var2 then delete;
run;

[MM88]

Thanks @Tom, your solution also works. Regards.