Looping through SAS Rows

0
votes

I'm fairly new to SAS, (I mainly use R, Python, and VBA) and I am looking for a way to loop through the rows of a SAS data set. Essentially, the R equivalent would be:

my_vector = c()
for(k in 1:10) {
if(k > 1 & k < 10) {
   my_vector[k-1] = mean(df[(k-1):(k+1), 1])
{
}

My main goal is to create an MA filter to estimate trend (I could do my homework in R, but I'm trying to learn SAS better).

Or, another solution would be to convert a specific row of a SAS data set into an array from PROC IML but I can't figure out how to get the column converted into an array.

Any help would be greatly appreciated.

2
loopssas
Do you have SAS/ETS licensed? Why invent your own when SAS provides procedures for this? – Tom
Can you post example input data (preferably in the form as SAS data step) and example results data? – Tom
SAS loops through rows automatically in a data step. Each row is processed independent of others - unless you explicitly keep/retain data. So you don't need a loop. Are you trying to calculate a moving average? – Reeza
If it is moving average the following may help. gist.github.com/statgeek/27e23c015eae7953eff2 but the ETS/PROC EXPAND mentioned is a great suggestion as well. – Reeza
It can be achieved through a data step as well. Please refer the following link. Click on the full code tab support.sas.com/kb/25/027.html – Nirvik Banerjee

2 Answers

1
votes

There are some pitfalls with the LAG function as per this article

If you are having trouble with it, use PROC TRANSPOSE to pivot your data and process as an array, comparing columns (if var_1 = 'YES' and var_2 = 'YES' then new_var = 'BLUE'), or manually create an array which might be more advanced than you want right now.

0
votes

From what I understand, your looking for a way to look back and also look ahead of the current row. If that is correct then the main solution is the lag() function. This looks back on what ever variable you place within the lag() function. It can also go back multiple rows lag2() lag3(). Additionaly the dataset needs to be sorted by the laged variable. Now looking ahead is tricky, no function is available for this, but you can use multiple set statements to accomplish this goal.

Here is an example of the looking ahead from Mark Keintz's paper

 data lead_example5;
     set sample1;

     if eof1=0 then
          set sample1 (firstobs=2 keep=close rename=(close=LEAD1)) end=eof1;
     else lead1=.;

    if eof3=0 then
         set sample1 (firstobs=4 keep=close rename=(close=LEAD3)) end=eof3;
    else lead3=.;
 run;

Also here is an example of the lag() function also from 

data example1;
    set sample1;

    close_1=lag(close);
    close_3=lag3(close);

    if close_1 ^=. then return_1 = close/close_1 - 1;
    if close_2 ^=. then return_3 = close/close_3 - 1;
run;