topic Re: Retaining first variable in column for future use in SAS Programming

Retaining first variable in column for future use

cyatkp — Sat, 13 Jan 2024 18:40:27 GMT

Hi, I have a column that is partially populated in which the first row contains a non-zero value and the remaining rows contain zeros. I would like to update the rows containing zeros with the first value in the row. I have tried using retain and FIRST.value functions to no avail.

In the example below first row contains 3. I need SAS to read this first value, retain it and update the

remaining rows with 3.

Thanks!

Re: Retaining first variable in column for future use

Tom — Sat, 13 Jan 2024 21:13:00 GMT

You seem to be using VARIABLE/ROW/COLUMN in a confusing way. A SAS dataset consists of a set of OBSERVATIONS that each have VALUES for all of the VARIABLES in the dataset. A ROW could be considered the same as an observation. A COLUMN could be considered the same as a VARIABLE. But you cannot have a first variable in a variable (column).

Sounds like you want to retain the value from the first OBSERVATION onto all of the following observations.

The simplest way is to read in the dataset twice once with just the first observation and only that one variable and the second time without that variable.

data want;
  if _n_=1 then set have(keep=X);
  set have(drop=X);
run;

There is no utility in trying to retain a variable that is coming in from a dataset. First of all, such variable as already retained. But more importantly the retained value is replaced when the next observation is read. So you will need to make a NEW variable so you can RETAIN the first value.

If you don't need the original variable (the one with the zeros) then DROP it. If you would like the new variable to use the same name as the old then add a RENAME statement.

data want;
  set have;
  if _n_=1 then newvar=x;
  retain newvar;
  drop x;
  rename newvar=x;
run;

You will have to use the second option if you have some other grouping variable so that you want to carry forward the first value for each group rather than just the value from the first observation in the dataset.

data want;
  set have;
  by group;
  if first.group then newvar=x;
  retain newvar;
  drop x;
  rename newvar=x;
run;

Re: Retaining first variable in column for future use

Kurt_Bremser — Sat, 13 Jan 2024 20:08:17 GMT

So you want to carry forward the value of a variable from the first observation to following observations.

(this is how it's written with SAS; in a database context, you would use column and row instead of variable and observation).

Is it really only the first observation of the dataset you want to carry forward, or should it be the first observation of a group carried forward within that group? If yes, show us a better example of your dataset so we can see how groups are identified.

You also say you want to update the zero values; should non-zero values remain as they are?

Re: Retaining first variable in column for future use

Patrick — Sun, 14 Jan 2024 02:07:10 GMT

To just retain any value greater zero and repeat until the next value greater zero below code should do.

data have;
  input var;
  datalines;
3
0
0
0
4
0
;

data want(drop=_:);
  set have;
  retain _tmp_var;
  if var>0 then _tmp_var=var;
  var=_tmp_var;
run;

proc print data=want;
run;

If you need something like above by group then just have your source data sorted by group and use by group processing with if first.<group_var> then ...

Re: Retaining first variable in column for future use

FreelanceReinh — Sun, 14 Jan 2024 10:00:23 GMT

Or this:

data want;
_n_=var;
set have;
if var=0 then var=_n_;
run;