Creating prompt for missing and nonmissing

问题:

I am trying to add prompt for missing or nonmissing options. This code doesn't work ,need to fix that. rec_And_issues is a new table that I created in the report. Need to pick rec_and_issues.SFVFDBK_FEEDBACK_COMMENTS is missing or not.

 %macro missing_or_nonmissing;
%if "&sel_issue" eq "Missing" %then %do;
DATA rec_and_issues;
set rec_and_issues;

 rec_and_issues.SFVFDBK_FEEDBACK_COMMENTS is null;
run;
%end;

%else %if "&sel_issue" eq "NonMissing" %then %do;
DATA rec_and_issues; 
set rec_and_issues;
rec_and_issues.SFVFDBK_FEEDBACK_COMMENTS is not null;
run;  
%end;
%mend missing_or_nonmissing;

回答1:

You shouldn't put the data step inside the macro. How exactly you decide to do this depends on style - I like to not include the WHERE in the macro if it's easily avoided, as this makes it easier for someone to read and understand your code - but any variation on this should be fine. Only put parts of the datastep in the macro that actually vary.

%macro missing_or_nonmissing(sel=);
  %let not = %sysfunc(ifc(&sel=NonMissing,not,));
  SVFDBK_FEEDBACK_COMMENTS is &not. null
%mend missing_or_nonmissing;

DATA rec_and_issues;
  set rec_and_issues;
  where %missing_or_nonmissing(sel=&sel_issue) ;
run;

No reason to do anything beyond that. Further, if you are using something as a parameter, use it as a parameter. Global variables shouldn't be used inside a macro in most cases, and definitely not in this case where it's clearly a parameter to the macro.

Further, you probably don't need to do this at all. If this is solely filtering the dataset, you almost certainly can do this whenever you actually use the dataset (or when it was created, depending on how it was created). For example, if your next step is a PROC SORT, as it often is, you should just do this in the PROC SORT - and this macro lets you do that. (This is why I like to leave WHERE out of it - since where syntax differs in data set options).

proc sort data=rec_and_issues(where=(%missing_or_nonmissing(sel=&sel_issue.)));
by idvar;
run;

Finally, if you're the one creating the prompt, I recommend having the underlying values be 1/0 not text. That way you don't have to worry about upcase/etc., and you can use them a bit more easily (since 1 is 'true' and 0 is 'false').

回答2:

Your data-steps were not in the correct syntax. You've used some form of sql two-level variable naming that doesn't work in a SAS data-step. A WHERE statement would work. I've rewritten the macro:

%macro missing_or_nonmissing;
%if "&sel_issue" eq "Missing" %then %do;
DATA rec_and_issues;
  set rec_and_issues;

  where SVFDBK_FEEDBACK_COMMENTS is null;
run;
%end;

%else %if "&sel_issue" eq "NonMissing" %then %do;
DATA rec_and_issues; 
  set rec_and_issues;

  where SFVFDBK_FEEDBACK_COMMENTS is not null;
run;  
%end;
%mend missing_or_nonmissing;

Although you could make the macro even shorter, as only the operator changes:

%macro missing_or_nonmissing;
%if "&sel_issue" eq "Missing" %then %let op=;
%else %if "&sel_issue" eq "NonMissing" %then %let op=not;
%else %goto exit;

DATA rec_and_issues;
  set rec_and_issues;

  where SVFDBK_FEEDBACK_COMMENTS is &op. null;
run;

%exit:
%mend missing_or_nonmissing;

Add these lines below to run the macro:

%let sel_issue = Missing; * or NonMissing;

%missing_or_nonmissing;

Your code will overwrite the rec_and_issues table when this macro runs, either with the SVFDBK_FEEDBACK_COMMENTS values being missing or not. To make a new table instead (and keep the rec_and_issues table) put a different table name in the data statement.

回答3:

Building on Joe's answer, you could even make it work without a macro at all, just using macro variables, as in the following:

%let not = %sysfunc(ifc(&sel=NonMissing,not,));

DATA rec_and_issues;
  set rec_and_issues;
  where SVFDBK_FEEDBACK_COMMENTS is &not null;
run;