May 25, 2011

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.
sas macro split
Note:
/**
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);
sas macro split
Output2 after applying the macro
%fill(test,out2,numvars=count3 count4,charvars=shop ,fillnums=0,fillchars=A);
sas macro split