SAS Macro to fill missing values in a dataset with specified values for numeric and character values..

Often a times, we get a dataset with missing values for certain variables…And for reporting purposes we need to convert them some or all of those variable values to default values (for e.g 0 for numeric values or NULL for character values)..

Try using this macro for such type of purposes…

For eg.

data test;

input fruit $ shop $ count2 count3 count4 ;

datalines;

apple wal . 2 3

coconut . 30 3 .

. sams 40 . .

eggs . 50 .

. . . 4 5

;

run;

Input dataset with couple of missing values for numeric(count1, 2,3,4 ) and character (fruit, shop) variables.

Note:

Code for %splitmacroval(numvars,num,numcnt,dlm=’ ‘) is at
SAS macro to split a macro variables values to multiple macro variables values and also get the split count into another variable

/**

Use the macro like this...

inpdsn- name of the input dataset

outdsn- name of the output dataset

numvars= - give the list of numeric variables to be filled in with

charvars= - give the list of character variables to be filled in with

fillnums= the numeric value to use to fill the dataset

fillchars=the character value to use to fill the dataset

%fill(test,out,fillnums=0,fillchars=A);

**/

%macro fill(inpdsn,outdsn,numvars=,charvars=,fillnums=,fillchars=);

data &outdsn; /*output dataset name*/

set &inpdsn;

%if &numvars eq ALL %then

%do;

array nums(*) _numeric_ ; /*assign all numeric variables to the array nums */

%end;

%else

%do;

%splitmacroval(numvars,num,numcnt,dlm=' ')

array nums(&numcnt.) %do k=1 %to &numcnt; &&num&k  %end;; /*assign all numeric variables to the array nums */

%end;

%if &charvars eq ALL %then

%do;

array chars(*) _character_ ; /*assign all character variables to the array chars */

%end;

%else

%do;

%splitmacroval(charvars,chr,chrcnt,dlm=' ')

array chars(&chrcnt.) %do k=1 %to &chrcnt; &&chr&k  %end;; /*assign all character variables to the array chars */

%end;

/* for every numeric variable assign 0 to the variable if the value is missing */

do i=1 to dim(nums);

if nums(i)=. then nums(i)=&fillnums;

end;

/* for every Character variable assign A for eg. to the variable if the value is missing */

do i=1 to dim(chars);

if chars(i)='' then chars(i)="&fillchars";

end;

run;

%mend fill;

Output1 after applying the macro

%fill(test,out1,numvars=ALL,charvars=ALL,fillnums=0,fillchars=A);

Output2 after applying the macro

%fill(test,out2,numvars=count3 count4,charvars=shop ,fillnums=0,fillchars=A);