Desktop productivity for business analysts and programmers

How do I remove blank rows from my data?

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

How do I remove blank rows from my data?

I have a data set that I'm working with that comes to me missing rows in random places. It looks something like this:

 

Ref_No     Loc       Amnt

1               NM        $50

2               WA        $42

3               ND         $71

 

 

4               ME         $28

5               CA          $93

6               CT          $62

 

I've managed to read the data into SAS EG by telling the load editor to read to a specific column and row, but when I go to sort the data, in order to take out the blank rows using the following code, the program stops reading when it reaches a blank row which leaves out a whole bunch of data.

 

proc sort data=work.original_file out=work.new_file;
by Ref_No;
where Ref_No ne .;
run;

 

Any idea what I can use to remove the blank rows without the program cutting off rows below the blank ones?

 

Thanks.


Accepted Solutions
Solution
‎05-04-2017 12:29 PM
Super User
Posts: 11,123

Re: How do I remove blank rows from my data?

You may want to verify that Ref_no is numeric. Are you getting any messages about numeric to character conversion.

 

Generally it is more reliable to use

where not missing(variablename) ;

as the missing function will work for either numeric or character.

 

And actually I would go back to the data step that reads the data and add:

 

if not missing(ref_no);

 

to remove the problem bits earlier.

View solution in original post


All Replies
Solution
‎05-04-2017 12:29 PM
Super User
Posts: 11,123

Re: How do I remove blank rows from my data?

You may want to verify that Ref_no is numeric. Are you getting any messages about numeric to character conversion.

 

Generally it is more reliable to use

where not missing(variablename) ;

as the missing function will work for either numeric or character.

 

And actually I would go back to the data step that reads the data and add:

 

if not missing(ref_no);

 

to remove the problem bits earlier.

PROC Star
Posts: 1,146

Re: How do I remove blank rows from my data?

Yuck! Don't you love crappy source data?

 

This happens to me quite a lot. My preference is to read the whole record into a temporary variable, and then parse out the bits that I want. If the quality is acceptable, I keep it.

 

Something like this:

 

data have(drop=_Smiley Happy;

length _InRec $32767;

input;

_InRec = _infile_;

if ^missing(_InRec)

then do;

/* other processes to check if we have what we want */

Var1 = scan(_InRec, 1);

Var2 = scan(_InRec, 2);

Var3 = scan(_InRec, 3);

/* other processes to check if we have what we want */

/* assume we set GoodFlag to 1 or 0 if the data are acceptable */

/* if acceptable conditions then */

_GoodFlag = 1;

/* else _GoodFlag = 0 */

if _GoodFlag then

output;

end;

cards;

1 2 3

4 6

7 8 9

11

12 13

14 15 16

run;

New Contributor
Posts: 2

Re: How do I remove blank rows from my data?

These are both great solutions, thank you so much!

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 233 views
  • 1 like
  • 3 in conversation