Date prompt issues with EG 4.2/sas 9.2

2010-05-08T09:41:00.000-07:00

Recently we upgraded our SAS 9.1.3 environment to SAS 9.2. SAS has changed its prompt functioning in SAS 9.2 for date type parameters which will cause problem for Where clauses having filter on date field. These problems are discussed in below given notes :

http://support.sas.com/kb/35/792.html

http://support.sas.com/kb/36/581.html

1. In SAS 9.1 when we created the Date parameter using parameter manager it used to accept the date value in "DDMONYYYY"d format. With inception of SAS 9.2 they provided some additional features for the prompts like you can pick up the date from calender , additional types of prompts and dynamically pick up the values. While providing all these facilities , looks like they missed one thing where date value is in form of DDMONYYYY instead of "DDMONYYYY"d format.

SAS fixed this defect in SAS 9.2 maintenance release where they provided one additional checkbox as shown in picture. This will convert the date prompt value from DDMONYYYY format to "DDMONYYYY"d format using macro _EG_WHEREPARAM and will give expected results.

2. Migrating SAS 9.1 project to 9.2 consisting code note with filter on Date parameter value :

SAS defined a macro named _EG_WHEREPARAM used to convert the prompt in correct format according to the data type. One thing we need to remember about this macro is that the macro is not GLOBAL Macro and can be used ONLY in Enterprise guide. So when you are migrating from 9.1.3 to 9.2 and your project consists of code note having filter on Date prompt as given below :

PROC SQL;

Select -----

From Table_name

WHERE Date_column = & Date_Column ;

quit;

you need to modify the sas program to include the macro call which will convert the date prompt in required format based on the operator you have used for comparison . Some examples are as given below :

EQUAL

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, EQ, TYPE=D )

NOT EQUAL

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, NE, TYPE=D, MATCHALL_CLAUSE=0 )

LESS THAN EQUAL TO

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, LE, TYPE=D )

GRATER THAN EQUAL TO

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, GE, TYPE=D )

LESS THAN

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, LT, TYPE=D )

GREATER THAN

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, GT, TYPE=D )

BETWEEN

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, BETWEEN, TYPE=D, MAX=End_Date )

NOT BETWEEN

%_eg_WhereParam( t1.EDR_TRADE_DT, Begin_Date, NOT BETWEEN, TYPE=D, MATCHALL_CLAUSE=0, MAX=End_Date )

3. Problem with Stored process :

As I already mentioned earlier the macro _EG_WHEREPARAM is not global so when you create a stored process using the code not you modified to include the _EG_WHEREPARAM macro it will give you warning message

WARNING: Apparent invocation of macro _EG_WHEREPARAM not resolved

and eventually your stored process execution will fail with syntax error due to DDMONYYYY formatted prompt value. In order to avoid this problem you can add _EG_WHEREPARAM macro to global macro library and execute the stored process.

Somehow after implementation of SAS 9.2_M1 , we can't see the macro code in logs so we requested tech support guys to send us the macro code for _EG_WHEREPARAM macro which is as given below :

%macro _EG_WhereParam( COLUMN, PARM, OPERATOR, TYPE=S, MATCHALL=_ALL_VALUES_,
MATCHALL_CLAUSE=1, MAX= );
%local q1 q2;
%if %QUPCASE(&TYPE) eq S or %QUPCASE(&TYPE) eq STRING %then
%do;
%let q1=%str(%");
%let q2=%str(%");
%end;
%else %if %QUPCASE(&TYPE) eq D or %QUPCASE(&TYPE) eq DATE %then %do;
%let q1=%str(%");
%let q2=%str(%"d);
%end;
%else %if %QUPCASE(&TYPE) eq T or %QUPCASE(&TYPE) eq TIME %then
%do;
%let q1=%str(%");
%let q2=%str(%"t);
%end;
%else %if %QUPCASE(&TYPE) eq DT or %QUPCASE(&TYPE) eq DATETIME %then
%do;
%let q1=%str(%");
%let q2=%str(%"dt);
%end;
%else
%do;
%let q1=;
%let q2=;
%end;

%if "&PARM" = "" %then %let PARM=&COLUMN;

%local IsBetween;
%let IsBetween = ("%QUPCASE(&OPERATOR)"="BETWEEN" or
"%QUPCASE(&OPERATOR)"="NOT BETWEEN");

%if "&MAX" = "" %then %do;
%let MAX = &parm._MAX;
%if &IsBetween %then %let PARM = &parm._MIN;
%end;

%if not %symexist(&PARM) or (&IsBetween and not %symexist(&MAX)) %then
1;
%else %if "%qupcase(&&&PARM)" = "%qupcase(&MATCHALL)" %then
&MATCHALL_CLAUSE;
%else %if (not %symexist(&PARM._count)) or &IsBetween %then %do;
&COLUMN &OPERATOR %unquote(&q1)&&&PARM%unquote(&q2)
%if &IsBetween %then
AND %unquote(&q1)&&&MAX%unquote(&q2);
%end;
%else %do;
%if (&&&PARM._COUNT = 0) %then %do;
not &MATCHALL_CLAUSE
%end;
%else %if (&&&PARM._count = 1) %then %do;
&COLUMN &OPERATOR (%unquote(&q1)&&&PARM%unquote(&q2))
%end;
%else %do;
&COLUMN &OPERATOR ( %do i=1 %to &&&PARM._count; %if &i > 1 %then %do; ,
%end;
%unquote(&q1)&&&PARM&i%unquote(&q2) %end; )
%end;
%end;
%mend;

This will help to deal with the problems associated with migration of SAS project consisting Date parameters from 9.1 to 9.2.

SAS 9.2 and BI Applications

Date prompt issues with EG 4.2/sas 9.2

WARNING: Apparent invocation of macro _EG_WHEREPARAM not resolved