BookmarkSubscribeRSS Feed
cyatkp
Calcite | Level 5

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!

 

3

0

0

0

0

0

4 REPLIES 4
Tom
Super User Tom
Super User

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;

 

Kurt_Bremser
Super User

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?

Patrick
Opal | Level 21

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;

Patrick_0-1705197985474.png

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 ...

 

FreelanceReinh
Jade | Level 19

Or this:

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1476 views
  • 1 like
  • 5 in conversation