SAS-Problem-4:Replacing blanks In a Dataset with Zeros or “N/A”



Replacing blanks In a Dataset with Zeros or “N/A”



 Have you ever wished that there should have been “Find and Replace” functionality in SAS similar to ‘CTRL + H’ in excel. Imagine a situation where you want to replace all the blank character values in a SAS Dataset with say “N/A” or where you would like to replace all “.” numeric fields with Zeros (0).
Suppose you have a dataset which is formed by running this code.
DATA ABC;
INPUT SERIAL NAME$ AGE UNITS STATE$;
CARDS;
1 DAVID 35 6 AP
2 RAJ 25 . LA
. . 56 8 .
4 ANDREW 34 . NY
;
RUN;
The output looks something like this.
Problem: I would like to replace all blank Character fields in this dataset with “N/A” and all “.” ‘s in numeric fields with zeros.
Solution:  As we know SAS reads the data row by row so what we have to do is to somehow tell SAS to in each row go column by column. So it reads for every row it reads all the columns.
Whenever we have to repeat some process iteratively we use generally “DO” loop which is what we are going to do here the code used is this:
DATA  ABC_2(DROP = I);
SET  ABC;
ARRAY CHARLIST(*) _CHAR_;
DO I = 1 TO DIM(CHARLIST);
IF CHARLIST(I) = "" THEN CHARLIST(I)="N/A";
END;
ARRAY NUMLIST(*) _NUMERIC_;
DO I = 1 TO DIM(NUMLIST);
IF NUMLIST(I) = . THEN NUMLIST(I)=0;
END;
IF SERIAL_NUM = 0 THEN  SERIAL_NUM = .;
RUN;
This is what is happening here in the following part
ARRAY CHARLIST(*)………CHARLIST(I)="N/A";
END;

We define an array “Charlist” which has dimension which is equal to the count of Character fields and has values of all character fields, similar to this later in the code I define “Numlist” which has dimensions equal to the number of Numeric fields and has the numeric fields as its values. Then we run a do loop in both cases from 1 to the respective dimensions.

In the end there is a statement which readsIF SERIAL_NUM = 0 THEN  SERIAL_NUM = .;” this is if a particular field should not have dots replaced with zeros.   

Blog Widget by LinkWithin

Search this blog..

Loading