comparison for only max month of present year&last year same month

Reply
Occasional Contributor
Posts: 15

comparison for only max month of present year&last year same month



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

Super User
Posts: 5,386

Re: comparison for only max month of present year&last year same month

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 never sleeps
Super User
Posts: 9,867

Re: comparison for only max month of present year&last year same month

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

Ask a Question
Discussion stats
  • 2 replies
  • 203 views
  • 0 likes
  • 3 in conversation