topic %Macro is not working in SAS Programming

%Macro is not working

anballa — Tue, 24 Jul 2018 14:59:50 GMT

I am trying to run the macro below and generate the records I need based on the prompt selections in SAS Enterprise Guide. All of the macros used within the code below are prompt the user would fill in prior to the code executing. The prompt &disp_records requires the selection of a single value from a list, and all of the other date prompts are manually entered in the format that is required. when I run the macro, no errors are produced. When I run the PROC SQL after the macro, it says work.test_records does not exist.

I am wanting the macro to run based on the &disp_records prompt. the the Proc SQL will add a few more parameters that are common regardless of the &disp_records prompt selection. Those records should produce the new table "work.results". I am probably missing something extremely simple. Please help.

%macro records();
%if &disp_records = 1 %then
    %do;
      data work.test_records;
set library1.table;
where infodate1 ge infodate2;
run;
    %end;
%else %if &disp_records = 2 %then
    %do;
      data work.test_records;
set library1.table;
where substr(end_date_ts,1,10) = '2222-12-31';
run;
    %end;
%else %if &disp_records = 3 %then
    %do;
      data work.test_records;
set library1.table;
where date_ts ge &date_ts_min;
and date_ts le &date_ts_max;
run;
    %end;
%mend records();

%records();

PROC SQL;
   CREATE TABLE WORK.results AS
   SELECT *
      FROM work.test_records
      WHERE(%_eg_WhereParam( (source), match_source, IN, TYPE=S, IS_EXPLICIT=0 )
   AND %_eg_WhereParam( (data_date), data_date_min, BETWEEN, TYPE=D, MAX=data_date_max, IS_EXPLICIT=0 ));
QUIT;

Re: %Macro is not working

ballardw — Tue, 24 Jul 2018 15:35:59 GMT

Set

options mprint symbolgen mlogic;

before the step where you execute the macro.

and run the code to show in the log the code generated, the values of created/manipulated macro variables and the results of macro logic statements (%if %do and such). Copy and paste the log results in a code box using the forums {i} icon.

The options will also place any notes such as 0 records were selected or such in a better context than you may be getting now.

Turn off the options with

options nomprint nosymbolgen nomlogic;

after the macro call.

One possibility is that your macro variable &disp_records has a value other than 1, 2 or 3.

Or if &disp_records =1 then none of the values meet the condition where infodate1 ge infodate2; for library1.table or similar that the where statements don't have any records for the other &disp_records values.

Note SAS would allow using

where &date_ts_min le date_ts le &date_ts_max;

which may be easier to recognize/ read the code when memory gets a bit stale then the "and".

Unless you are going to add data manipulation that is unique to each &disp_records value you might consider something similar to :

%macro records();
  data work.test_records;
    set library1.table;

  %if &disp_records = 1 %then
    %do;
      where infodate1 ge infodate2;
    %end;
  %else %if &disp_records = 2 %then
    %do;
      where substr(end_date_ts,1,10) = '2222-12-31';
    %end;
  %else %if &disp_records = 3 %then
    %do;
       where date_ts ge &date_ts_min;
       and date_ts le &date_ts_max;
    %end;
 run;

%mend records();

to emphasize that the purpose of the &disp_records is to set where clause. Also if none of the where clause are selected then work.test_records would be the entire contents of library1.table. Which may mitigate the empty or missing work.test_records issue.

Re: %Macro is not working

Astounding — Tue, 24 Jul 2018 15:47:03 GMT

@ballardw's suggested rewrite is good under normal circumstances, but could hide the problem here. If the problem is that &DISP_RECORDS is actually 5, your original program will generate the error that you mentioned. But this rewrite will create the output data set (with no subsetting applied). Until the program is debugged, you could start out by temporarily adding this as the first statement within your macro:

%put DISP_RECORDS is &disp_records;

The other suggestions, such as temporarily turning on MPRINT, are excellent.