Macro in Proc Sql Statement

[AAWTomHanks]

Does anyone know if I could possibly shorten this up with a macro or something else?

PROC SQL;

CREATE TABLE J4 AS

SELECT DISTINCT t1.pc,

           t1.CODE,

             (MAX(t1.JAN2010)) AS MAX_of_JAN2010,

             (MAX(t1.JUL2010)) AS MAX_of_JUL2010,

             (MAX(t1.NOV2010)) AS MAX_of_NOV2010,

             (MAX(t1.DEC2010)) AS MAX_of_DEC2010,

             (MAX(t1.JAN2011)) AS MAX_of_JAN2011,

             (MAX(t1.FEB2011)) AS MAX_of_FEB2011,

             (MAX(t1.MAR2011)) AS MAX_of_MAR2011,

             (MAX(t1.APR2011)) AS MAX_of_APR2011,

             (MAX(t1.MAY2011)) AS MAX_of_MAY2011,

             (MAX(t1.JUN2011)) AS MAX_of_JUN2011,

             (MAX(t1.JUL2011)) AS MAX_of_JUL2011,

             (MAX(t1.AUG2011)) AS MAX_of_AUG2011,

             (MAX(t1.SEP2011)) AS MAX_of_SEP2011,

             (MAX(t1.OCT2011)) AS MAX_of_OCT2011,

             (MAX(t1.NOV2011)) AS MAX_of_NOV2011,

             (MAX(t1.DEC2011)) AS MAX_of_DEC2011

       FROM J3 t1

       GROUP BY t1.pc, t1.CODE;

QUIT;

[Haikuo]

If your month are random, there is little you can do. However, if your month are continuous, you could try some Macro like this:

data _null_;

  do _n_=1 by 1 until (date> '01dec2011'd);

  date=intnx('month','01oct2010'd,_n_,'b');

  call symputx('date'||strip(_n_), put(date,monyy7.));

  end;

run;

%macro test;

PROC SQL;

CREATE TABLE J4 AS

SELECT DISTINCT t1.pc,

  t1.CODE, %do i=1 %to 13; max(t1.&&date&i) as max_of_&&date&i, %end;

  (MAX(t1.DEC2011)) AS MAX_of_DEC2011

  FROM J3 t1

  GROUP BY t1.pc, t1.CODE;

QUIT;

%mend;

%test

Regards,

Haikuo

[Ksharp]

Do you have to use SQL ? I think proc means is better .

proc means data=have nway noprint;

class pc CODE;

var JAN2010--DEC2011;

output out=want max= /autoname;

run;

Ksharp