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.
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.
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.
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;
These are both great solutions, thank you so much!
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.