topic Re: Recursively filling rows in SAS Programming

Recursively filling rows

chris2377 — Fri, 09 Sep 2022 16:45:08 GMT

Hi,

Say I have a folllowing dataset:

data have;
	do i = 1 to 10;
		id = i;
		output;
	end;
	drop i;
run;
data have;
	set have;
	if id = 1 then var = 10;
run;

Now I want to fill empty rows in a recursive way, so that value of each row equals to the value of previous row multiplied by two (so the values are 20, 40, 80 etc)

I've tried to use retain statement in different ways but failed to achieve what I want.

Any help, please?

Best

Re: Recursively filling rows

Quentin — Fri, 09 Sep 2022 16:48:31 GMT

Please show the code you have tried, and describe whether you got errors or unexpected results. It will help others help you.

Re: Recursively filling rows

chris2377 — Fri, 09 Sep 2022 16:50:17 GMT

Here you are @Quentin . But it's obviuosly wrong:

data my_try;
	set have;
 	retain var;
	var = lag(var)*2;
 run;

Re: Recursively filling rows

Quentin — Fri, 09 Sep 2022 16:54:32 GMT

But close. Because you are retaining, you don't need lag.

Consider something like:

data have;
  do i = 1 to 10;
    id = i;
    output;
  end;
  drop i;
run;

data want;
  set have;

  retain var;

  if id = 1 then var = 10;
  else var = var*2;

  put (id var)(=) ;
run;

Re: Recursively filling rows

ballardw — Fri, 09 Sep 2022 16:57:12 GMT

Since RETAIN is one of the most likely ways to accomplish this you should show what you tried and explain where it failed.

Note RETAIN gets reset to a value if it is already in the data set. So if you attempted to Retain Var from the last 'Have' set it would get reset to missing for all except the first observation.

I don't see any "empty rows" to "fill" with anything. You have created a data set with missing values for a specific Variable.

Perhaps this is what you are looking for.

data want;
   set have;
   retain tempvar;
   if var then tempvar=var;
   else do;
      var=2*tempvar;
      tempvar=var;
   end;
   drop tempvar;
run;

This will reset to the Var value when not missing, if there are any.

@chris2377 wrote:

Hi,

Say I have a folllowing dataset:
data have;
	do i = 1 to 10;
		id = i;
		output;
	end;
	drop i;
run;
data have;
	set have;
	if id = 1 then var = 10;
run;
Now I want to fill empty rows in a recursive way, so that value of each row equals to the value of previous row multiplied by two (so the values are 20, 40, 80 etc)

I've tried to use retain statement in different ways but failed to achieve what I want.

Any help, please?

Best

Re: Recursively filling rows

ghosh — Sat, 10 Sep 2022 03:14:18 GMT

My understanding of recursion is a function that calls itself. Thus a macro or proc fcmp would be ideal

proc fcmp outlib=work.funcs.myfunc;
function bytwo(n);
/* if n=1 then x=n; else x = n * 2; */
  x=ifn(n=1,1,(n * 2));
return(x);
endsub;
quit;

options cmplib=work.funcs;

* Recursion example ; 
 
data allinone;
    do i = 1 to 10;	
       var=bytwo(i);
		output;
	end;
run;
proc print;
run;

Re: Recursively filling rows

mkeintz — Sat, 10 Sep 2022 01:17:28 GMT

Use the RETAIN statement for two purposes: (1) to carry over values to subsequent observations, and (2) to initialize VAR to the desired value of 10 for the first observation.

Then output BEFORE doubling var to carry over to the next observation:

data have;
  do id = 1 to 10;
    output;
  end;
run;

data want;
  set have;
  retain var 10;
  output;
  var=2*var;
run;

Re: Recursively filling rows

chris2377 — Mon, 12 Sep 2022 10:00:39 GMT

Thanks @ballardw , @ghosh , @mkeintz for your suggestions as well