proc sql;    select count(distinct cat) into :mv from tmp; quit; note: added: distinct, deleted: group by cat. ... View more

I know. Can you count how many time you have played mind-reading ? ... View more

Art, I believe OP just wanted to get rid of blanks. ... View more

macro compiles way ahead of regular SAS code. so if you leave &mv inside SQL, it will try to be resovled long before 'select into' taking effect. Haikuo ... View more

Not sure what do you mean by 'two rows'. is this what you want? proc sql;    select trim(stmt) into : macrovar1 separated by '' from tmp where cat=1;    select trim(stmt) into : macrovar2 separated by '' from tmp where cat=2; quit; %put %bquote(&macrovar1); %put   %bquote(&macrovar2); Regards, Haikuo ... View more

For your specific condition, you could try array(): data tmp3  tmp2 ; set tmp; n=0; array acc(*) acc:;    do _n_=1 to dim(acc);       if acc(_n_) not in (1,2,3,4,5,6) then do;            output tmp3; n+1; return; end;    end;    if n=0 then output tmp2;    drop n;    run; Regards, Haikuo ... View more

Since OP has not answered yet, my wild guess would be: data _null_; a='OMITTED'; put a $binary.; run; Regards, Haikuo ... View more

Can you post some samples of those incorrectly input columns? ... View more

So your data is not monthly basis, rather, they are averaged on seasonal basis? And you are trying to identify certain 'spiking' month? Can you post just a few line of your data? Regards, Haikuo ... View more

Well, if you have SAS/ETS, then proc expand seems to suit your need: data have; input a @@; cards; 1 2 3 4 3 5 6 2 10 5 6 1 ; proc expand data=have out=want; convert a=b/ transformout=(MOVRANGE 3); run; Above code is showing the how to obtain 'backward moving range' of 3 records. for your case, it will be 7 if your monthly data is outlined by rows. proc expand also supports  'forward moving range'. Regards, Haikuo Edit: you mentioned lag(), which is not a bad idea in my opinion: data want_lag; set have; range=range(a,lag(a),lag2(a)); run; Well, this is the case for 3, if 7 bothers you, you can always use array to cut off some coding labor. ... View more

If you want to play with do-loop, here is another approach: data want; do time=1 by 1 until (last.id);    set test;    by id;    output; end; run; Regards, Haikuo ... View more

I am still not sure I have completely understand what you want. I thought my solution could do what you just asked. if you could just post several line of data to show what your wanted table look like, that would definitely help. Regards, Haikuo ... View more