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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.