Lag operator in a 'do' list

[ConradLouw]

I need to use 11 Lag statements to reference up to 12 lines back. I'm unable to do it in a 'do' list and have to phisically type in...
Lag1
Lag2 up to
...
Lag11.

Is there a way that I can do something like
do i = 1 to 11;
lag(i)(...) = ...
instead of writing each lag part (lag1=... lag2=... lag11=)individually?

 

[klaz2002]

Lag doesn't work in a loop.

 

[ChrisW75]

Conrad - I think perhaps an array is the way to go here. You can load all your values into an array, then run a do loop on them and refer to any value you want.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[ConradLouw]

Hi. I need help on the following...
Im trying to use a macro. Inside my macro is a proc sql. I send a reference number to the macro, and the macro calculate a min and max corresponding to the number.
I cant do it. Can you use proc sql in a macro?

My program:

libname SAS "M:\2008\Admin\SAS Tool\Conrad - Bucketry";


%macro daman(count);
proc sql noprint;
select min(score) into: a
from sas.datacalc2
where scorebucket = count;

select max(score) into: b
from sas.datacalc2
where scorebucket = count;
quit;
%mend daman;

proc sort data = sas.datacalc2;
by score scorebucket;
run;

data sas.Outnow3;
set sas.outnow2;
by scorebucket;
%daman(count);
min = &a;
max = &b;

run;

 

[ChrisW75]

Yes, you can use proc sql in a macro. You can also use a macro in a datastep. However, what you cannot do is use a macro containing a datastep or procedure in a datastep. You'll be able to see why when you understand the way the macros resolve at runtime.

Calling a macro effectively replaces the macro call statement with the code contained in the macro. So effectively you're trying to run a proc SQL step within a datastep, which will basically end your datastep right there.
Also, your macro should look like this:-

%macro daman(count);
proc sql noprint;
  select min(score) into: a
  from sas.datacalc2
  where scorebucket = &count;

  select max(score) into: b
  from sas.datacalc2
  where scorebucket = &count;
quit;
%mend daman;

Here's a piece of code I use for running a macro multiple times according to the contents of the datastep.

* Get list of agent codes that I'm outputting *;
proc sort data=full_dat(keep=agent) out=agents nodupkey;
  by agent;
run;

* Use array to create list of macro variables agnt1, agnt2 etc *;
*  each one contains an agent number                           *;
data _null_;
  set agents nobs=num_agents end=eof;
  file print;
  array agnt{99} $2;
  retain agnt;

  agnt{_n_} = agent;
  if eof then do;
    call symput('agnt_cnt',num_agents);
    do i = 1 to num_agents;
      rec = compress('agnt' || i);
      call symput(rec,agnt{i});
    end;
  end;
run;


* Macro to output the data I want *;
%macro outpt(o_agnt);
data _null_;
  set full_dat(where=(agent="&o_agnt")); 
  file blah recfm=v lrecl=4000;
  put @1 allrec;
run;
%mend outpt;

* Macro to call output macro with correct agent code *;
*Row by row breakdown:-
  Loop from 1 to limit set by number of different agents
  let macro variable blah equal &agnt1, &agnt2 etc
  call macro outpt and pass it the value of the agent code for this iteration
*;
%macro loop;
  %do i=1 %to &agnt_cnt;
    %let blah = &&agnt&i;
    %outpt(&blah);
  %end;
%mend loop;

%loop;

You would want to put your macro in the OUTPT macro.

However, I think I can see a better way of doing what you're after in this case. Macros are not needed here.

proc sql noprint;
  create table A as
  select min(score) as min
  from sas.datacalc2
  group by scorebucket
  ;

  create table B as
  select max(score) as max
  from sas.datacalc2
  group by scorebucket
  ;
quit;

proc sort data=sas.outnow2;
  by scorebucket;
run;


data sas.Outnow3 
  merge sas.outnow2(in=in1)
        A
        B;
  by scorebucket;
  if in1;
run;

That should do it nicely without having to use a macro at all.



Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.