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=;
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
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);
@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.
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.
@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().
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.
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.
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);
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.