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

Posted by Analyst in SAS, SAS-Arrays, SAS-Programing, SAS-questions, SAS-Replacing Blanks, SAS-Replacing Dots

You are here: Home > SAS-Replacing Dots > SAS-Problem-4:Replacing blanks In a Dataset with Zeros or “N/A”

on Sep 15, 2009

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 reads “IF SERIAL_NUM = 0 THEN SERIAL_NUM = .;” this is if a particular field should not have dots replaced with zeros.