topic Re: Arrays retain values in SAS Programming

Arrays retain values

Naz10 — Tue, 08 Oct 2019 20:56:47 GMT

I have a code where I use an array of about 50 variables and perform lagged calculation.

data have;
input Date mmddyy10. Test_1 Test_2 Test_3;
datalines;
09/30/2019 200 150 100
10/31/2019 0.004 0.0076 0.0074
11/30/2019 0.006 0.0102 0.0129
12/31/2019 0.0035 0.0102 0.0129
01/31/2020 0.014 0.0101 0.089
02/29/2020 0.056 -0.0103 0.055
03/30/2020 0.056 0.0155 0.078
;
run;

%let out_list_index1 = Test_1 Test_2 Test_3;
%let begin_date="30Apr2020"d;
%put &out_list_index1;

data want;
set have;
array list{*} &out_list_index1;
do i=1 to dim(list) while(date <= &begin_date);
list(i)=lag(list{i})*(1+list{i});
end;
drop i;
run

However, I'm having trouble setting up the code correctly. The idea here is not just to lag original variables, but lag newly calculated variables, i.e. for new row of data grab the lag of the previous calculated row and use that to calculate the new row. Example below shows how the before and after (1 variable as an example)

E.g.

have:

var1

want:

var1

(2+1)*1=3

(3+1)*3=12

(4+1)*12=60

Thank you!

Re: Arrays retain values

Reeza — Tue, 08 Oct 2019 03:00:21 GMT

I don't think arrays are the correct solution here. In SAS arrays are only variable shortcuts and refer to data within a single row, not across rows.

data have;
input var1;
cards;
1
2
3
4
;;;;

data want;
set have;
retain var2;

if _n_ = 1 then var2=var1;
else var1 = (var1+1)*var2;

var2=var1;

run;

proc print;run;

@Naz10 wrote:

I have a code where I use an array of about 50 variables and perform lagged calculation.

data want;
set have;
array list(*) &out_list_index1;
do i=1 to dim(list) while(date <= &begin_date);
list(i)=lag(list(i))*(1+list(i)); /*grab new <list> */
end;
drop i;
run;

However, I'm having trouble setting up the code correctly. The idea here is not just to lag original variables, but lag newly calculated variables, i.e. for new row of data grab the lag of the previous calculated row and use that to calculate the new row. Example below shows how the before and after (1 variable as an example)

E.g.

have:

var1

1

2

3

4

want:

var1

1

(2+1)*1=3

(3+1)*3=12

(4+1)*12=60

Thank you!

Re: Arrays retain values

Naz10 — Tue, 08 Oct 2019 03:21:21 GMT

Thank you, Reeza, for the quick reply.

The reason I'm trying to use arrays is because I need to perform this type of calculation for about 50 variables.

But it seems, like you said, array function doesn't retain values across the rows.

Re: Arrays retain values

PGStats — Tue, 08 Oct 2019 03:21:57 GMT

Never use lag within a conditional code block. Use a temporary array instead. Temporary arrays are always retained.

data want;
set have;
array list{*} &out_list_index1;
array lag{999} _temporary_; /* Adjust 999 */
do i=1 to dim(list) while(date <= &begin_date);
	list{i}=lag{i}*(1+list{i});
	end;
do i=1 to dim(list);
	lag{i} = list{i};
	end;
drop i;
run;

(untested)

Re: Arrays retain values

Tom — Tue, 08 Oct 2019 12:56:40 GMT

@Naz10 wrote:

Thank you, Reeza, for the quick reply.

The reason I'm trying to use arrays is because I need to perform this type of calculation for about 50 variables.

But it seems, like you said, array function doesn't retain values across the rows.

The RETAIN statement is used to mark variables to have their values retained. (not cleared to missing at start of next iteration of the data step).

data want ;
  set have ;
  array old var1-var50;
  array new new1-new50;
  retain new1-new50;
  do over old;
    if _n_=1 then new=old;
    else new=(old+1)*new;
  end;
run;

Re: Arrays retain values

ballardw — Tue, 08 Oct 2019 15:45:37 GMT

@Naz10 wrote:

Thank you, Reeza, for the quick reply.

The reason I'm trying to use arrays is because I need to perform this type of calculation for about 50 variables.

But it seems, like you said, array function doesn't retain values across the rows.

I suggest providing a small example data set using maybe 3 of those 50 variables and just enough records to exercise your logic. Then show the result of what this process should look like for that example data set.

Best would be to provide the example data in the form of a data step so we can test code against it.

Re: Arrays retain values

Naz10 — Tue, 08 Oct 2019 19:01:30 GMT

Thank you. I updated the original post with a sample data.

Re: Arrays retain values

Naz10 — Wed, 09 Oct 2019 00:36:16 GMT

The code worked! Thank you! I modified it with a macro and here is the final code:

%let word_cnt=%sysfunc(countw(&out_list_index1));

data want;
set have;
array list{*} &out_list_index1;
array lag{&word_cnt} _temporary_;
do i=1 to dim(list) while(date <= &begin_date.);
list{i}=lag{i}*(1+list{i});
end;
do i=1 to dim(list);
lag{i} = list{i};
end;
drop i;
run;