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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.