CODEWITHSUNDEEP
macrossas
adjfac
adjfac

Reputation: 635

SAS Macro not going through the entire dataset

I am very new to SAS and try to learn the best practice of doing things. I tried to write a simple macro to pick out any given fields with a missing value in a particular dataset. The idea is to go through each record (row) and let the do(for) loop to act as an OR in an if statement. my questions are

  1. My understanding is that the macro - once called, should be executed for each row in my dataset, but in this case, it only seems to be executing it once (that is, the do loop only seem to execute once rather than 100 times).

  2. Is there better way to do this?

    /* say I have a dataset with fieldA fieldB fieldC fieldD etc. and 100 records, some of which were missing */

%let varList = fieldA fieldB; /* check fieldA and fieldB */
%ReportIncompleteFields(sasdata.myDAta, &varList., work.tempData);

%macro ReportIncompleteFields(inDataName, chkVars, outDataName);
%let numVars = %sysfunc(countw(&chkVars.));

length keepRrd 8;
keepRcd = 0;

data &outDataName.;
    set &inDataName.;
    %do ii = 1 %to &numVars.;
        %let iiVarName = %scan(&chkVars., &ii.);
        %if &iiVarName. = ''  %then keepRcd=keepRcd+1;
    %end;

    %if keepRcd=0 %then delete; 
run;

%mend ReportIncompleteFields;

Upvotes: 0

Views: 103

Answers (2)

Tom
Tom

Reputation: 51621

The problem here is not understanding what macro code does. The most common use of macro code is to generate SAS code. Your macro logic does not generate any SAS code.

Consider your first block of macro code:

%do ii = 1 %to &numVars.;
    %let iiVarName = %scan(&chkVars., &ii.);
    %if &iiVarName. = ''  %then keepRcd=keepRcd+1;
%end;

The value &iiVarName will be the name of one of the variables listed in the macro variable CHKVARS. That is it will be a string of characters like FieldA. That string of characters will never by equal to two single quotes next to each other. So the %THEN clause will never generate any code. Even if you did pass in '' as one of the variable names in CHKVARS the code that would be generated is missing the semi-colon needed to signal the end of the assignment statement. The semi-colon after the digit 1 will mark the end of the %IF statement.

The second %IF statement, %if keepRcd=0 %then delete;, can also never be true since the stream of letters keepRcd is never equal to the digit 0.

So if your goal is to only keep the records where none of the listed fields are missing then just use the CMISS() function. For example this program will separate the input dataset into a GOOD and BAD dataset.

data good bad ;
  set have ;
  if 0=cmiss(of fieldA fieldB) then output good;
  else output bad;
run;

It is so simple wrapping it into a macro is probably a waste of time.

Upvotes: 2

Reeza
Reeza

Reputation: 21294

SAS data step acts as a loop, you don't need macro logic here at all, at most an array. Look up the nmiss/cmiss or missing() functions. The key things is to make sure that your variables are all the same type, or declare two arrays one for numeric and one for character data. This assumes that all variables have the same type. If that assumption isn't true, create two parameters, one to list character one variables and one for numeric and extend the logic. 

%macro ReportIncompleteFields(inDataName, chkVars, outDataName);


data &outDataName.;
    set &inDataName.;
    array check(*) &chkVars;

    if nmiss(of check(*))=0;*keep all variables with no records missing;

run;

%mend ReportIncompleteFields;

I'm not sure what this is for, but SAS will also automatically excluding casewise missing data from most statistical procs or where it's required by default so you may not need this.

Upvotes: 1

Related Questions