BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
MM88
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Super User Tom
Super User

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
Calcite | Level 5
Thanks @Tom, your solution also works. Regards.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 351 views
  • 0 likes
  • 3 in conversation