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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.