Hi,
I have a below code which has macros and would like to simplify this in one step:
Please let me know
%let cmth = Apr;
%let dmth = Jul; %let lday = 31;
%let cyear = 2016; %let pyear = 2015;
%let sday = 01;
%let ayr = 2000; %let byr = 2001; %let cyr = 2002; %let dyr = 2003;
%let eyr = 2004; %let fyr = 2005; %let gyr = 2006; %let hyr = 2007;
%let iyr = 2008; %let jyr = 2009; %let kyr = 2010; %let lyr = 2011; %let myr = 2012; %let nyr = 2013; %let oyr = 2014;
DATA want;
SET test;
IF ((EFFECTIVE_DATE < "01Jan&&pyear"D OR EFFECTIVE_DATE > "31Dec&&pyear"D)
AND (PRODUCT_PBP = 'LIP1' OR PRODUCT_PBP = 'MAX1')
AND ((LAST_SURVEY_DATE >= "&sday&&dmth&&ayr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&ayr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&byr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&byr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&cyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&cyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&dyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&dyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&eyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&eyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&fyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&fyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&gyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&gyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&hyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&hyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&iyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&iyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&jyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&jyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&kyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&kyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&lyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&lyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&myr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&myr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&nyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&nyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&oyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&oyr"D));
Run;
Thanks,
)
One simplification: take every occurrence of && and change it to &.
Another concerns the conditions for EFFECTIVE_DATE, which could be rewritten as:
(year(EFFECTIVE_DATE) = &pyear)
For all the conditions that involve LAST_SURVEY_DATE, it's important to know more about what you plan on doing. Will you always use SDAY=01, and LDAY=last day of the month? Or might you be selecting some other block of days within the month?
Hi,
Thanks !
It must be changed every month. For example if we are running it for Apr then it must be Current Month plus 3 (July)
Sday should be always 01 and last dat it depends on the month ( either 29 or 30 or 31)
Year should be current year minus 1 ( we need to poulate the data from 2000 to 2014)
So this must be pretty close then:
%let m=4; /* April */
%let cyear = 2016;
%let pyear = %eval(&cyear - 1);
%let last_year = %eval(&cyear - 2);
%let first_year = %eval(&cyear - 16);
if (year(EFFECTIVE_DATE) = &pyear) and (PRODUCT_PBP = 'LIP1' OR PRODUCT_PBP = 'MAX1') and
(month(LAST_SURVEY_DATE) in (&m, %eval(&m+3))) and (&first_year <= year(LAST_SURVEY_DATE) <= &last_year);
There will definitely be some complications (not worked out at this point) if the month ever becomes 10 or more, making month + 3 greater than 12.
No need for macros, as far as I can see.
DATA want;
retain lyr mth;
SET test;
if _n_ = 1 then do;
mth = month(intnx("MONTH", today(), 3));
lyr = year(today()) - 1;
end;
IF year(EFFECTIVE_DATE) >= 2000 AND
year(EFFECTIVE_DATE) <= lyr AND
month(EFFECTIVE_DATE) = mth AND
PRODUCT_PBP in ('LIP1', 'MAX1');
drop lyr mth;
run;
Thanks PG.
How can we incorporate the Last_survey_date macro in the code ?Thanks
sday = 01
dmth should be current month +3 ( July)
ayr should be current year -2 ( from 2000 to 2014)
(LAST_SURVEY_DATE >= "&sday&&dmth&&ayr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&ayr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&byr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&byr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&cyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&cyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&dyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&dyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&eyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&eyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&fyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&fyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&gyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&gyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&hyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&hyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&iyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&iyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&jyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&jyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&kyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&kyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&lyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&lyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&myr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&myr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&nyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&nyr"D)
OR (LAST_SURVEY_DATE >= "&sday&&dmth&&oyr"D AND LAST_SURVEY_DATE <= "&lday&&dmth&&oyr"D)
OK, I might have gotten it this time
DATA want;
retain lyr mth;
SET test;
if _n_ = 1 then do;
mth = month(intnx("MONTH", today(), 3));
pyr = year(today()) - 1;
end;
IF year(EFFECTIVE_DATE) ne pyr AND
year(LAST_SURVEY_DATE) >= 2000 AND
year(LAST_SURVEY_DATE) < pyr AND
month(LAST_SURVEY_DATE) = mth AND
PRODUCT_PBP in ('LIP1', 'MAX1');
drop pyr mth;
run;
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.