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

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;

 

ObsDATESTOREPRODUCTPRICE
18/31/2017BB135
28/31/2017BB236
38/31/2017BB337

 

 

 

 

/* THE LOG */

 

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

2         OPTIONS PAGENO=MIN;

3         %LET _CLIENTTASKLABEL='Program';

4         %LET _CLIENTPROJECTPATH='';

5         %LET _CLIENTPROJECTNAME='';

6         %LET _SASPROGRAMFILE=;

7        

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

21        

22         GOPTIONS ACCESSIBLE;

23        

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;

39        

40        

41        

42         %LET NEW_DATE = '31AUG2017'D;

43         %LET OLD_DATE = '31JUL2017'D;

44        

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

48        

49         OPTIONS MPRINT MLOGIC;

50        

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;

66        

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.

68        

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

     

 

70        

71        

72        

73         GOPTIONS NOACCESSIBLE;

74         %LET _CLIENTTASKLABEL=;

75         %LET _CLIENTPROJECTPATH=;

76         %LET _CLIENTPROJECTNAME=;

77         %LET _SASPROGRAMFILE=;

78        

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

80         ODS _ALL_ CLOSE;

81        

82        

83         QUIT; RUN;

84        

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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);

View solution in original post

6 REPLIES 6
Tom
Super User Tom
Super User

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

 

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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.

Kurt_Bremser
Super User

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

ballardw
Super User

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.

LL5
Pyrite | Level 9 LL5
Pyrite | Level 9

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:

ObsDATESTOREPRODUCTPRICE
17/31/2017AA125
27/31/2017AA226
37/31/2017AA327

 

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:

ObsDATESTOREPRODUCTPRICE
18/31/2017BB135
28/31/2017BB236
38/31/2017BB3

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.

Tom
Super User Tom
Super User

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);

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
  • 6 replies
  • 4323 views
  • 3 likes
  • 4 in conversation