comparison for only max month of present year&last year
I HAVE DATA LIKE THIS
DATA DS1;
INPUT DATE : DDMMYY10. AMOUNT COMMAMT INSENTIVAMT BONUSAMOUNT;
FORMAT DATE DDMMYY10.;
CARDS;
31/01/2014 5000 6000 2345 4567
28/02/2014 6000 7000 5775 4587
30/03/2014 7000 8000 5464 4323
31/12/2013 3400 9000 2456 8970
30/11/2013 5000 6000 3456 7865
28/10/2013 6000 5000 4456 6758
28/05/2013 6000 7000 4563 7890
30/04/2013 7000 3400 6754 7856
31/03/2013 3400 5000 4567 2532
28/02/2013 5000 6000 7890 1324
31/01/2013 6000 3400 3456 3456
;
RUN;
MY REQUIREMENT IS
ONLY MAX(MONTH) OF MAX YEAR AND COMPARISON PURPOSE BEFORE YEAR SAME MONTH LIKE MARCH DATA ONLY IN PRESENT&PREVIOUS YEAR
30/03/2014 7000 8000 5464 4323 AND 31/03/2013 3400 5000 4567 2532.
I TRIED IN PROC SQL CAME PRESENT YEAR ONLY,
HELP ME
THANKS IN ADVANCE
You could use the INTNX() function in a join condition (max() result join back with original table) to find the corresponding date -1 year.
DATA DS1; INPUT DATE : DDMMYY10. AMOUNT COMMAMT INSENTIVAMT BONUSAMOUNT; FORMAT DATE DDMMYY10.; CARDS; 31/01/2014 5000 6000 2345 4567 28/02/2014 6000 7000 5775 4587 30/03/2014 7000 8000 5464 4323 31/12/2013 3400 9000 2456 8970 30/11/2013 5000 6000 3456 7865 28/10/2013 6000 5000 4456 6758 28/05/2013 6000 7000 4563 7890 30/04/2013 7000 3400 6754 7856 31/03/2013 3400 5000 4567 2532 28/02/2013 5000 6000 7890 1324 31/01/2013 6000 3400 3456 3456 ; RUN; proc sql ; select * into : date from ds1 having date=max(date) union select * from ds1 where year(date)+1 eq %substr(&date,7) and month(date)=%substr(&date,4,2) having date=max(date); quit;
Xia Keshan
Message was edited by: xia keshan
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.