topic Re: How to correctly use %IF %Then %Else inside a macro in SAS Programming

How to correctly use %IF %Then %Else inside a macro

LL5 — Thu, 28 Sep 2017 18:37:48 GMT

Hi. Everyone. I have a dataset test like below. My goal is to set up a logic which is: If the date is less than the new date, then new filter will be applied. Otherwise, old filter will be applied.

I have two questions which I hope I could get some advices from everyone here.

Question 1: Since I have July, August and September data in dataset test and I didn’t specify the date value in the Macro Price, how could the program tell the first if then condition is false? (shown as below) and returns the result consists of August’s data?

MLOGIC(PRICE): %IF condition DATE LT &NEW_DATE is FALSE

Question 2: How should I apply this If Then Else logic inside the macro correctly, so I could get old date with old filter and new date with new filter accordingly?

Thanks.

DATA TEST;

FORMAT DATE MMDDYY10.;

INPUT DATE MMDDYY10. STORE$ PRODUCT$ PRICE;

CARDS;

7/31/2017 A A1 25

7/31/2017 A A2 26

7/31/2017 A A3 27

8/31/2017 B B1 35

8/31/2017 B B2 36

8/31/2017 B B3 37

9/30/2017 C C1 45

9/30/2017 C C2 46

9/30/2017 C C3 47

;

RUN;

%LET NEW_DATE = '31AUG2017'D;

%LET OLD_DATE = '31JUL2017'D;

%LET FILTER_OLD=%STR(STORE="A");

%LET FILTER_NEW=%STR(STORE="B");

%PUT &FILTER_NEW &FILTER_OLD &NEW_DATE &OLD_DATE ;

OPTIONS MPRINT MLOGIC;

%MACRO PRICE;

PROC SQL;

CREATE TABLE PRICE AS

SELECT

DATE,

STORE,

PRODUCT,

PRICE

FROM TEST

%IF DATE LT &NEW_DATE %THEN

WHERE &FILTER_OLD AND DATE=&OLD_DATE;

%ELSE

WHERE &FILTER_NEW AND DATE=&NEW_DATE;;

QUIT;

%MEND;

%PRICE;

PROC PRINT DATA=PRICE; RUN;

Obs	DATE	STORE	PRODUCT	PRICE
1	8/31/2017	B	B1	35
2	8/31/2017	B	B2	36
3	8/31/2017	B	B3	37

/* THE LOG */

;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL='Program';

4 %LET _CLIENTPROJECTPATH='';

5 %LET _CLIENTPROJECTNAME='';

6 %LET _SASPROGRAMFILE=;

8 ODS _ALL_ CLOSE;

9 OPTIONS DEV=ACTIVEX;

10 GOPTIONS XPIXELS=0 YPIXELS=0;

11 FILENAME EGSR TEMP;

12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR

13 STYLE=HtmlBlue

14 STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")

15 NOGTITLE

16 NOGFOOTNOTE

17 GPATH=&sasworklocation

18 ENCODING=UTF8

19 options(rolap="on")

20 ;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

22 GOPTIONS ACCESSIBLE;

24 DATA TEST;

25 FORMAT DATE MMDDYY10.;

26 INPUT DATE MMDDYY10. STORE$ PRODUCT$ PRICE;

27 CARDS;

NOTE: The data set WORK.TEST has 9 observations and 4 variables.

NOTE: Compressing data set WORK.TEST increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: DATA statement used (Total process time):

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.01 seconds

memory 783.78k

OS Memory 20896.00k

Timestamp 09/28/2017 11:16:52 AM

Step Count 12 Switch Count 58

Page Faults 182

Page Reclaims 162

Page Swaps 0

Voluntary Context Switches 182

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 280

37 ;

38 RUN;

42 %LET NEW_DATE = '31AUG2017'D;

43 %LET OLD_DATE = '31JUL2017'D;

45 %LET FILTER_OLD=%STR(STORE="A");

2 The SAS System 11:08 Thursday, September 28, 2017

46 %LET FILTER_NEW=%STR(STORE="B");

47 %PUT &FILTER_NEW &FILTER_OLD &NEW_DATE &OLD_DATE ;

STORE="B" STORE="A" '31AUG2017'D '31JUL2017'D

49 OPTIONS MPRINT MLOGIC;

51 %MACRO PRICE;

52 PROC SQL;

53 CREATE TABLE PRICE AS

54 SELECT

55 DATE,

56 STORE,

57 PRODUCT,

58 PRICE

59 FROM TEST

60 %IF DATE LT &NEW_DATE %THEN

61 WHERE &FILTER_OLD AND DATE=&OLD_DATE;

62 %ELSE

63 WHERE &FILTER_NEW AND DATE=&NEW_DATE;;

64 QUIT;

65 %MEND;

67 %PRICE;

MLOGIC(PRICE): Beginning execution.

MPRINT(PRICE): PROC SQL;

MLOGIC(PRICE): %IF condition DATE LT &NEW_DATE is FALSE

MPRINT(PRICE): CREATE TABLE PRICE AS SELECT DATE, STORE, PRODUCT, PRICE FROM TEST WHERE STORE="B" AND DATE='31AUG2017'D;

NOTE: Compressing data set WORK.PRICE increased size by 100.00 percent.

Compressed is 2 pages; un-compressed would require 1 pages.

NOTE: Table WORK.PRICE created, with 3 rows and 4 columns.

MPRINT(PRICE): QUIT;

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.01 seconds

memory 5340.40k

OS Memory 26020.00k

Timestamp 09/28/2017 11:16:52 AM

Step Count 13 Switch Count 32

Page Faults 0

Page Reclaims 39

Page Swaps 0

Voluntary Context Switches 76

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 272

MLOGIC(PRICE): Ending execution.

69 PROC PRINT DATA=PRICE; RUN;

NOTE: There were 3 observations read from the data set WORK.PRICE.

NOTE: PROCEDURE PRINT used (Total process time):

real time 0.00 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

3 The SAS System 11:08 Thursday, September 28, 2017

memory 776.12k

OS Memory 20896.00k

Timestamp 09/28/2017 11:16:52 AM

Step Count 14 Switch Count 24

Page Faults 0

Page Reclaims 22

Page Swaps 0

Voluntary Context Switches 25

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 8

73 GOPTIONS NOACCESSIBLE;

74 %LET _CLIENTTASKLABEL=;

75 %LET _CLIENTPROJECTPATH=;

76 %LET _CLIENTPROJECTNAME=;

77 %LET _SASPROGRAMFILE=;

79 ;*';*";*/;quit;run;

80 ODS _ALL_ CLOSE;

83 QUIT; RUN;

Re: How to correctly use %IF %Then %Else inside a macro

Tom — Thu, 28 Sep 2017 19:07:53 GMT

@LL5 wrote:

Question 1: Since I have July, August and September data in dataset test and I didn’t specify the date value in the Macro Price, how could the program tell the first if then condition is false? (shown as below) and returns the result consists of August’s data?

MLOGIC(PRICE): %IF condition DATE LT &NEW_DATE is FALSE

Question 2: How should I apply this If Then Else logic inside the macro correctly, so I could get old date with old filter and new date with new filter accordingly?

Thanks.

Number one is easy. The macro processor just works on text strings and knows nothing of data steps or SQL statements. So it is comparing the actual letters DATE to the value of you macro variable.

For number two you should just include the comparison of the DATE value into your logic. If did want to make run time decisisions in an SQL statement based on the values on the data then you should use a CASE statement.

So a simple translation should convert to this.

PROC SQL;
  CREATE TABLE PRICE AS
    SELECT DATE
         , STORE
         , PRODUCT
         , PRICE
    FROM TEST
    WHERE ( DATE LT &NEW_DATE and &FILTER_OLD AND DATE=&OLD_DATE)
       or (DATE GE &NEW_DATE and &FILTER_NEW AND DATE=&NEW_DATE)
  ;
QUIT;

But that doesn't really make sense. It almost looks like you could just reduce to this? Or is sometimes &NEW_DATE older than &OLD_DATE? Perhaps a clearer explaination of what you want will help.

PROC SQL;
  CREATE TABLE PRICE AS
    SELECT DATE
         , STORE
         , PRODUCT
         , PRICE
    FROM TEST
    WHERE (&FILTER_OLD AND DATE=&OLD_DATE)
       or (&FILTER_NEW AND DATE=&NEW_DATE)
  ;
QUIT;

In general you need to first write out the code you need the macro to create. Then you can decide how to use macro logic and/or macro variables to generate that code.

Re: How to correctly use %IF %Then %Else inside a macro

ballardw — Thu, 28 Sep 2017 19:14:02 GMT

The macro language does not "see" the values of your dataset variables.

When you use

%IF DATE LT &NEW_DATE %THEN

The macro processor is comparing the actual text "DATE" to the value of the macro variable &New_date.

Please see this for an example of the comparison likely actually conducted:

data _null_;
   if 'D' lt "'" then put "D lt '";
   else put "D is not lt '";
run;

since string comparisons will stop at the first unequal result.

If you want to do something condtionally inside proc sql then the appropriate sturcture would usually be a CASE statement but I'm not quite sure exactly what you attempting to accomplish. You would have to have a reasonable text litteral value or macro variable holding a date value in lieu of DATE varaible name for the macro comparsion to have a chance of working.

You might consider showing what the desired result is supposed to be, or at least indicate so if that print output is it.

Re: How to correctly use %IF %Then %Else inside a macro

LL5 — Thu, 28 Sep 2017 19:42:14 GMT

Thanks Tom. The general concept is prior to Aug 31, I would apply an old logic in my where statement by using the old filter. On and after Aug 31, I would need to use a new logic with a new filter.

The example I demonstrated here is not a good example. In my real work situation, new_date (which is August) would never change, because that is the date when the new logic was implemented. Old_date could be any date prior to August or after August. My ultimate goal is to only change the value of old_date and get the desired result.

That means if I define old_date as July 31, I would get July 31’s data with old filter. If I define old_date as Sept 31, I would get Sep 31’s data with new filter.

I know this could be done in many alternative ways, but I want to know if this could be done by using this If then else concept inside the macro.

Thanks again.

Re: How to correctly use %IF %Then %Else inside a macro

LL5 — Thu, 28 Sep 2017 20:00:01 GMT

Thanks Ballardw. What I was try to do is to tell the program if the "old_date" (July 2017) is less than "new_date" (Aug 2017 and this won't change), I hope to see the July's data with the "old_filter". I hope to see the below:

Obs	DATE	STORE	PRODUCT	PRICE
1	7/31/2017	A	A1	25
2	7/31/2017	A	A2	26
3	7/31/2017	A	A3	27

If the "old_date" (let say it's Aug 2017) is equal to the "new_date" (Aug 2017 again), I hope to see the Aug's data with the "new_filter". Result would be like below:

Obs	DATE	STORE	PRODUCT	PRICE
1	8/31/2017	B	B1	35
2	8/31/2017	B	B2	36
3	8/31/2017	B	B3	37

Lastly, if the "old_date" (Sept 2017) is greater than the "new_date"(Aug 17), i would hope to see a dataset with Sept's data with the "new_filter".

Be noted I didn't use a good example here, but this is generally what I was trying to do - set the "new_date" to be unchanged at Aug 17 , define the "old_date" based on actual period of time that I need and apply the different filters accordingly.

Re: How to correctly use %IF %Then %Else inside a macro

Tom — Thu, 28 Sep 2017 20:56:41 GMT

I still don't see the need for macro logic here. The purpose of macro logic is to conditionally conditionally generate the code to use. It is not to test the values of variables in your data. (Although an complicated macro could run some code to test values of variables and set flags that it uses to decide which pieces of code to generate.)

So if we continue with this OLD/NEW analogy you could make a macro that takes a parameter with two choices OLD or NEW and based on the users choice when calling the macro it will generate different SAS code. For exmaple you have it generate different WHERE clauses on an SQL query.

A trivial example:

%macro test(which);
proc sql noprint;
  create table want as
    select * from have
%if (%upcase(&which)=OLD) %then %do;
     where source='OLD'
%end; 
%else %do;
     where source='NEW'
%end;
  ;
quit;
%mend test;
%test(OLD);

If you want to test date literals in macro logic then make sure to use %SYSEVALF() as normal %EVAL() does not understand date literals or floating point numbers.

%macro test(asof);
proc sql noprint;
  create table want as
    select * from have
%if %sysevalf(&asof <= '01AUG2016'd) %then %do;
     where source='OLD'
%end; 
%else %do;
     where source='NEW'
%end;
  ;
quit;
%mend test;
%test(asof='01JAN2015'd);

Re: How to correctly use %IF %Then %Else inside a macro

Kurt_Bremser — Fri, 29 Sep 2017 06:09:14 GMT

@LL5 wrote:

Thanks Tom. The general concept is prior to Aug 31, I would apply an old logic in my where statement by using the old filter. On and after Aug 31, I would need to use a new logic with a new filter.

The example I demonstrated here is not a good example. In my real work situation, new_date (which is August) would never change, because that is the date when the new logic was implemented. Old_date could be any date prior to August or after August. My ultimate goal is to only change the value of old_date and get the desired result.

That means if I define old_date as July 31, I would get July 31’s data with old filter. If I define old_date as Sept 31, I would get Sep 31’s data with new filter.

I know this could be done in many alternative ways, but I want to know if this could be done by using this If then else concept inside the macro.

Thanks again.

You do NOT need macro logic for that. Just store your cutoff date in a macro variable and use that (as a replacemenet for a literal) in a compound where condition:

where
  (date lt &cutoff_date and (old_condition))
  or
  (date ge &cutoff_date and (new_condition))

The macro processor is a code generator that does its work before steps are compiled and run. It does NOT interact with in-step logic, unless you use functions and call routines like symget() or call symput().