topic Re: Array from first row of a group to be a fraction of the last row of the previous group in SAS Programming

Array from first row of a group to be a fraction of the last row of the previous group

RDzh — Mon, 03 Feb 2025 16:12:48 GMT

Hello all,

I'd like to set a numeric array in all rows of a second group to be a fraction of the corresponding values in last row of the previous group. Code-wise, this is the (obviously flawed) program I'm trying to write:

data have;

infile datalines delimiter =',';

input Group :$1. val1 val2 :3.;

datalines;
1,5,8
1,4,10
1,7,11
2,7,1
2,5,9
;
run;

data want;
set have;
by group;
array num _numeric_; /* range over the numeric values*/
if group = '2' then do; /* go to group 2*/
	if first.group then do; /* go to first observation of group 2*/
		do over num;
			format num best6.2;
			num = 0.95*lag(num); /* first row of group 2 set to 95% of last row of previous group*/
		end;

	else do;
		do over num; /* remainder rows of 2nd group*/
			num = lag(num); /* all subsequent rows of group 2 equal to 1st row of group 2*/
		end;
	end;
end;
run;

I've come up with a roundabout way of doing it, but I'm sure there will be a quick fix. Please post any such you can come up with.

Kind regards

Re: Array from first row of a group to be a fraction of the last row of the previous group

Tom — Mon, 03 Feb 2025 17:18:17 GMT

You need to be much more specific about what you want.

The way you described it you appear to want to modify the values in observation 4 only (the first observation of the second group).

What happens in observation 5 (the second observation for the second group)?

What happens in observations 1 to 3 (the first group)?

What happens in the third group?

Calling LAG conditionally is going to cause a lot of confusion. LAG returns the values you have previously passed it. If you don't call LAG() on the last observation of the previous group then there is no way for it know how to return that value later.

This seems to be what described.

data have;
  input Group $ val1 val2 ;
datalines;
1 5 8
1 4 10
1 7 11
2 7 1
2 5 9
;

data want;
  set have;
  by group;
  retain lag1 lag2 ;
  if last.group then do; lag1 = val1; lag2 = val2; end;
  else if first.group and _n_>1 then do;
    val1 = lag1 * .95 ;
    val2 = lag2 * .95 ;
  end;
run;

Which gives this result:

Re: Array from first row of a group to be a fraction of the last row of the previous group

ballardw — Mon, 03 Feb 2025 19:07:44 GMT

Can you show an actual completed example of what you expect to create? I can't follow what you have described.

data want;
set have;by group;
array num _numeric_; /* range over the numeric values*/
if group = '2' then do; /* go to group 2*/
	if first.group then do; /* go to first observation of group 2*/ <= this comment indicates misunderstanding of what FIRST
                                                                           or LAST variables are
		do over num;
			format num best6.2;
			num = 0.95*lag(num); /* first row of group 2 set to 95% of last row of previous group*/
		end;

	else do;
		do over num; /* remainder rows of 2nd group*/
			num = lag(num); /* all subsequent rows of group 2 equal to 1st row of group 2*/
		end;
	end;
end;
run;

Re: Array from first row of a group to be a fraction of the last row of the previous group

RDzh — Mon, 03 Feb 2025 19:43:10 GMT

Thanks, Tom
This kindaaa does what I'm trying to get to.

As to being "much more specific", I literally say what I'd like help with: "all rows of a second group to be a fraction of the corresponding values in last row of the previous group". I don't say anything else because I don't want anything else.

Admittedly, though, I create confusion by speaking in the topic title about the first row and then about all rows - it's just once I've figured how to change the first row I'll be able to change the rest of the rows too.

Re: Array from first row of a group to be a fraction of the last row of the previous group

RDzh — Mon, 03 Feb 2025 19:34:57 GMT

Starting with

data have;
  input Group $ val1 val2 ;
datalines;
1 5 8
1 4 10
1 7 11
2 7 1
2 5 9
;

I'd like to get to

data want;
  input Group $ val1 val2 ;
datalines;
1 5 8
1 4 10
1 7 11
2 6.95 10.45
2 6.95 10.45
;

Re: Array from first row of a group to be a fraction of the last row of the previous group

Tom — Mon, 03 Feb 2025 19:46:25 GMT

@RDzh wrote:

Thanks, Tom
This kindaaa does what I'm trying to get to.

As to being "much more specific", I literally say what I'd like help with: "all rows of a second group to be a fraction of the corresponding values in last row of the previous group". I don't say anything else because I don't want anything else.

So that is much clearer than your first attempt. But it does not cover what values you want for the first group.

You will need NEW variables to be able to do this. You can always add some drop and/or rename statements to make them use the original names.

For simplicity let's just do it for ONE variable first.

Also let's assume you want the new variable to be MISSING on the first group.

data want;
   set have;
   by group;
   retain new1;
   output;
   if last.group then new1 = .95 * val1 ;
run;

Result

Obs    Group    val1    new1

 1       1      5.00     .
 2       1      4.00     .
 3       1      7.00     .
 4       2      6.65    6.65
 5       2      5.00    6.65

To extend it to multiple variables just add arrays.

data want;
  set have;
  by group;
  array old val1-val2;
  array new new1-new2;
  retain new1-new2;
  output;
  if last.group then do over old;
    new = old * 0.95;
  end;
run;

If you want non-missing values for the first group then explain what values you want.