SAS retain value and assign to new variable

Question

I have the following data

EMPID   XVAR    SRC 
ABC     PER1    1   
ABC             2   
XYZ     PER1    1   
XYZ             2   
LMN     PER1    1   
LMN             2   
LMN     PER2    1   
LMN             2   
LMN             2   
LMN     PER3    1   
LMN             2

I need to create a new variable _XVAR for records where SRC=2 based on the value for XVAR on the previous record (where SRC=1)

The output should be like:

EMPID   XVAR    SRC  _XVAR
ABC     PER1    1     
ABC             2     PER1
XYZ     PER1    1   
XYZ             2     PER1
LMN     PER1    1   
LMN             2     PER1
LMN     PER2    1   
LMN             2     PER2
LMN             2     PER2
LMN     PER3    1   
LMN             2     PER3

I am trying the following, but it isnt working;

data t003;
set t003;
by EMPID;
retain XVAR;
if SRC eq 2 then _XVAR=XVAR;
run;

score 1 · Answer 1 · answered Jun 13 '18 at 07:59

1

You can use LAG to retrieve prior row values and conditionally use that value in an assignment.

Sample data

data have; input
EMPID $ XVAR $  SRC; datalines;
ABC     PER1    1   
ABC     .       2   
XYZ     PER1    1   
XYZ     .       2   
LMN     PER1    1   
LMN     .       2   
LMN     PER2    1   
LMN     .       2   
LMN     .       2   
LMN     PER3    1   
LMN     .       2   
run;

Example code

data want;
  set have;

  lag_xvar = lag(xvar);

  if src eq 2 then do;
    if lag_xvar ne '' then _xvar = lag_xvar;
  end;
  else
    _xvar = ' ';

  retain _xvar;
  drop lag_xvar;
run;

answered Jun 13 '18 at 07:59

Richard

25,390
3
25
38

Thanks Richard. This almost works for me the way I want however no value is assigned for the second record for EMPID='LMN' where src=2. – babsdoc Jun 13 '18 at 08:25
Strange. When I run the code in my answer, the output agrees with what you showed in the question under *The output should be like:* – Richard Jun 16 '18 at 11:59

Ruben ten Cate · Accepted Answer · 2018-06-15T08:31:28.467

1

It can also be done by saving the XVAR in a new variable (last_XVAR), retaining it and dropping it (you dont want it in the output). Then use that one to assign _XVAR. Note that you need to set last_XVAR after the IF, or the current XVAR is used in the assignment of _XVAR.

Your code, edited:

data t003;
    set t003;
    by EMPID;

    length _XVAR last_XVAR $ 10;

    if SRC eq 2 then _XVAR = last_XVAR;

    last_XVAR = XVAR;
    retain last_XVAR;
    drop last_XVAR;
run;

edited Jun 15 '18 at 08:31

answered Jun 13 '18 at 11:42

Ruben ten Cate

26
2

Hi Ruben, I was looking for something like this and your code is close to what I had in mind (without using LAG), however for some weird reason, SAS is considering all variables as numeric in this datastep and gives me a log full of NOTES and WARNINGS. – babsdoc Jun 13 '18 at 14:18
Hi babsdoc, I am not sure why this happens, but you can solve it by adding a length statement. I have added it to my code sample. Make sure to change the length of the variables to your requirements. – Ruben ten Cate Jun 15 '18 at 08:30

SAS retain value and assign to new variable

2 Answers2