LAG( ) or DO-LOOP?

[lancearmstrong1313]

I have the following data:

X Y Z
1 2 .
. 3 .
. 4 .
. 5 .
. 6 .

For the first row of data, I have:

X=1
Y=2
Z=X+Y -> 3. Z will always = X+Y. However, The 2nd row X = 1st row Z. So after computing the first Z, my dataset looks like:

X Y Z
1 2 3
3 3 .
. 4 .
. 5 .
. 6 .

Then the 2nd Z=6

X Y Z
1 2 3
3 3 6
6 4 .
. 5 .
. 6 .

....
....

this repeats. Overall, Z depends on X and Y but X depends on the previous Z. I need to find some way to do this automatically and I'm not quite sure how to tell SAS that Z=X+Y and that X=Z from the previous row.

I think I may need to either use a do-loop or use the lag() function somehow but not sure....any help would be greatly appreciated!

Thanks!

[Janus]

Well, this is certainly a difficult one, and I suspect there is an easier way, but this will work. (I tried several ways to do this without a macro but could not find a solution.)

data test;
input X Y Z;
datalines;
1 2 .
. 3 .
. 4 .
. 5 .
. 6 .
run;

%macro test(data_in=,data_out=);
	proc sql noprint; select monotonic() into:row_nums separated by ' ' from test; quit;
	proc sql noprint; select max(monotonic()) into:column_length from test; quit;
	data &data_out.; set &data_in.;
	%let k=1;
	%let row_op = %scan(&row_nums., &k.);
	%do %while(&k. < &column_length.);
		z=x+y;
		newz=lag1(z);
		if _n_ <= &row_op. then x=x; else x=newz;
		%let k=%eval(&k.+1);
		%let row_op = %scan(&row_nums., &k.);
	%end;
	z=x+y;
	drop newz;
	run;
%mend test;

%test(data_in=test,data_out=test2);

[lancearmstrong1313]

Thanks for the code, Janus. I also suspect there should be an easier way - I posted it on this forum hoping someone had come across a similar problem before. I feel like a combination of do-loops and lag() should work.

For each do-loop, use the lag() function for the Z variable and then simply say X=lag(Z). Then calculate the new Z value based on X (which = lag(Z) and Y).

Anyway, thanks for the code! I'll give it a shot

Could someone perhaps explain specifically what exactly the SQL statements are searching for or doing?

[Janus]

If you successively X with lag1(Z) you will always end up with at least one missing value. When you go to replace Z with X+Y then at least one row will attempt to add a missing value. It is probably possible somehow, but I could not see an obvious way.

The SQL statements are simply assigning macro variables and saving you inputs. Monotonic() is SAS's command for row number in SQL statements, so the first SQL statement just throws the row numbers as a list into a macro variable. The second is just capturing the total number of rows in the dataset. This makes the macro function usable on datasets of any size.

[lancearmstrong1313]

Thanks! I use both SAS and SQL but have not encountered the monotonic() function.

[Link]

This may work.

data given;
input X Y;
datalines;
1 2
. 3
. 4
. 5
. 6
run;

data test;
   set given;
   retain Z;
   if not(x=.) then Z=x+y;
   else Z=Z+y;
   new_x=z-y;
run;

[lancearmstrong1313]

Yes, that worked as well!