topic Re: Count the number of nonmissing values in a macro in SAS Programming

Count the number of nonmissing values in a macro

mk5 — Thu, 15 Feb 2018 23:46:35 GMT

Hi, I am trying to check the sample sizes (number of non-missing values) for both a categorical and quantitative variable, within a macro, and print a subtitle if either of them is less than 30. I have tried probably 30 variations of assigning the sample sizes to macro variables and always have some kind of error.

%macro ttest2(dsn= , quantvar= , catvar= , siglev= , doplot= );
%let s1 = %sysfunc(count(&quantvar));
%let s2 = %sysfunc(count(&catvar));
%if (&s1 < 30 or &s2 < 30) %then %do;
title2 "Warning: sample sizes of &s1 and &s2 may be too small for valid inference";
%end;

...

If anyone could tell me how to assign the sample sizes to variables within my macro, I would really appreciate it! Thank you!

Re: Count the number of nonmissing values in a macro

Astounding — Fri, 16 Feb 2018 01:19:45 GMT

As a general rule, macro language does not process data. It constructs programming statements. So the %LET statements have nothing to do with the contents of any variables in any SAS data sets.

What values are you hoping to assign to S1 and S2?

Re: Count the number of nonmissing values in a macro

mk5 — Fri, 16 Feb 2018 01:29:29 GMT

I want s1 to be the number of nonmissing values for the given quantitative variable, and s2 to be the number of nonmissing values for the given categorical variable. I am supposed to find these within the macro.

Re: Count the number of nonmissing values in a macro

Tom — Fri, 16 Feb 2018 01:49:11 GMT

@mk5 wrote:

I want s1 to be the number of nonmissing values for the given quantitative variable, and s2 to be the number of nonmissing values for the given categorical variable. I am supposed to find these within the macro.

Then have the macro generate some SAS code that could find those counts for you.

%macro ttest2(dsn= , quantvar= , catvar= , siglev= , doplot= );
%local s1 s2 ;
proc sql noprint;
%let s1=0;
%let s2=0;
select count(&quantvar)
     , count(&catvar)
  into :s1 trimmed
     , :s2 trimmed
  from &dsn 
;
quit;
%if (&s1 < 30 or &s2 < 30) %then %do;
  title2 "Warning: sample sizes of &s1 and &s2 may be too small for valid inference";
%end;
... rest of macro code ...

If you mean the number of distinct non-missing values then add the DISTINCT keyword inside the COUNT() aggregate function.

count(distinct &quantvar)

Re: Count the number of nonmissing values in a macro

Patrick — Fri, 16 Feb 2018 01:50:42 GMT

@mk5

Always try to first write your code without macro and only when that works wrap macro code around. As others already wrote: SAS Macros don't process SAS tables. They are mainly use to implement dynamic code.

Here a way to go:


%macro ttest2(dsn= , quantvar= , catvar= , siglev= , doplot= );
  data _null_;
    set &dsn end=last;
    array _NonMiss [2] 8 _temporary_;
    _NonMiss[1]=sum(_NonMiss[1],missing(&quantvar) = 0);
    _NonMiss[2]=sum(_NonMiss[2],missing(&catvar) = 0);
    if last then
      do;
        call symputx('s1',_NonMiss[1], l);
        call symputx('s2',_NonMiss[2], l);
      end;
  run;

  %if (%eval(&s1 < 30) or %eval(&s2 < 30)) %then 
    %do;
      title2 "Warning: sample sizes of &quantvar and &catvar may be too small for valid inference";
    %end;
  %else 
    %do;
      title2 "all good";
    %end;
  proc print data=sashelp.class(obs=1);
  run;
%mend;


data sampleDS1 sampleDS2;
  set sashelp.class;
  if _n_>10 then call missing(age);
  output; output;
  output sampleDS2;
run;

%ttest2(
  dsn=sampleDS1 , 
  quantvar=age , 
  catvar=name 
  );

%ttest2(
  dsn=sampleDS2 , 
  quantvar=age , 
  catvar=name 
  );