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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

3 REPLIES 3
ballardw
Super User

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
Onyx | Level 15

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
Calcite | Level 5

These are both great solutions, thank you so much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 8417 views
  • 2 likes
  • 3 in conversation