How do I remove blank rows from my data?

[dsw243]

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.

[ballardw · Accepted solution]

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.

[TomKari]

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=_:);

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;

[dsw243]

These are both great solutions, thank you so much!