Using retain to create two variables at the same time?

[Alppen]

Hi,

I have a given variable which I want to use to dynamically create two new variables. For context, this is related to calculating life tables from mortality data. Here's the starting point:

data test1;
input a;
datalines;
0.1
0.2
0.1
0.2
;
run;

The dataset I want to create is one where variable b starts at an arbitrary number (here 100), c is defined as a*b, and all the following values for b are lag(b)-lag(c), as follows:

data want;
input a b c;
0.1 100 10
0.2 90 18
0.1 72 7.2
0.2 64.8 12.96
;
run;

I tried using retain and lag, but something is off as the assignment of c uses a from the same row and b from the previous row, whereas I would like it to use b from the same row as well. So here's my effort that doesn't work as intended:

data test2;
set test;
retain b 100;
c = a*b;
lag_c = lag(c);
if lag_c ne . then b = b - lag_c;
run;

The solution and my mistake is probably pretty basic, but having mostly an R background these functionalities are still somewhat unintuitive, so any help is welcome!

[FreelanceReinh · Accepted solution]

Hi @Alppen and welcome to the SAS Support Communities!

You can compute b after an OUTPUT statement and thus omit the LAG function:

data want;
set test1;
retain b 100;
c=a*b;
output;
b=b-c;
run;

[Alppen]

Wow, that was fast, thanks!

[andreas_lds]

You don't need lag at all, retaining b and an output-statement is all you need:

data test2;
   set test1;

   retain b 100;

   c = a * b;

   output;

   b = b - c;
run;

Edit: i really should have Firefox reload a page before starting to write an answer.

[ed_sas_member]

Hi @Alppen 

You can do this:

data want;
	set test1;
	if _N_=1 then do;
		b = 100;
		c= a*b;
	end;
	else do;
		retain c;
		b+(-c);
		c= a*b;
	end;
run;