BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
anballa
Fluorite | Level 6

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;

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

View solution in original post

2 REPLIES 2
ballardw
Super User

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.

 

Astounding
PROC Star

@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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 6385 views
  • 0 likes
  • 3 in conversation