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;
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
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
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 the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.