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
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.
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.
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;
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: