topic Macro in Proc Sql Statement in SAS Procedures

Macro in Proc Sql Statement

AAWTomHanks — Wed, 02 May 2012 15:16:07 GMT

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;

Re: Macro in Proc Sql Statement

Haikuo — Wed, 02 May 2012 15:53:01 GMT

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

Re: Macro in Proc Sql Statement

Ksharp — Thu, 03 May 2012 04:33:50 GMT

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