topic Re: How do I remove blank rows from my data? in SAS Enterprise Guide

How do I remove blank rows from my data?

dsw243 — Thu, 04 May 2017 16:13:59 GMT

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.

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

ballardw — Thu, 04 May 2017 16:17:53 GMT

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.

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

TomKari — Thu, 04 May 2017 16:23:55 GMT

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

12 13

14 15 16

run;

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

dsw243 — Thu, 04 May 2017 16:29:43 GMT

These are both great solutions, thank you so much!