BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
wwuuuww
Calcite | Level 5

Hi all,

 

I'm a sas beginner struggling quite a bit with retain. 

 

I have daily data where when a specific condition is met, I want to preserve that value for the subsequent two days, so in essence create the following value_lagged variable.

 

Date          Condition    Value     Value_lagged

1/1/2005         1               5                 0

1/2/2005         0               0                 5 

1/3/2005         0               0                 5

1/4/2005         0               2                 0

1/5/2005         0               3                 0

1/6/2005         0               0                 0

1 ACCEPTED SOLUTION

Accepted Solutions
PeterClemmensen
Tourmaline | Level 20
data have;
input Date:mmddyy10. Condition Value;
format Date mmddyy10.;
datalines;
1/1/2005 1 5
1/2/2005 0 0
1/3/2005 0 0
1/4/2005 0 2
1/5/2005 0 3
1/6/2005 0 0
;

data want;
   set have;
   Value_lagged=0;
   lag1Condition=lag1(Condition);
   lag2Condition=lag2(Condition);
   lag1Value=lag1(Value);
   lag2Value=lag2(Value);
   if lag1Condition=1 then do;
      Value_lagged=lag1Value;
   end;
   if lag2Condition=1 then do;
      Value_lagged=lag2Value;
   end;
   drop lag:;
run;

View solution in original post

6 REPLIES 6
PeterClemmensen
Tourmaline | Level 20
data have;
input Date:mmddyy10. Condition Value;
format Date mmddyy10.;
datalines;
1/1/2005 1 5
1/2/2005 0 0
1/3/2005 0 0
1/4/2005 0 2
1/5/2005 0 3
1/6/2005 0 0
;

data want;
   set have;
   Value_lagged=0;
   lag1Condition=lag1(Condition);
   lag2Condition=lag2(Condition);
   lag1Value=lag1(Value);
   lag2Value=lag2(Value);
   if lag1Condition=1 then do;
      Value_lagged=lag1Value;
   end;
   if lag2Condition=1 then do;
      Value_lagged=lag2Value;
   end;
   drop lag:;
run;
wwuuuww
Calcite | Level 5
Sorry about the late response, but thanks! All responses were great but this was the simplest way to get at what I needed.
Kurt_Bremser
Super User

Try this:

data want;
set have;
retain count 99 value_lagged;
output; * this output is necessary because we don't want value_lagged set immediately;
if condition then do;
  count = 0;
  value_lagged = value;
end;
count + 1;
if count > 3 then value_lagged = 0;
drop count;
run;

 

Edit: added drop statement.

mkeintz
PROC Star

This is a case where LAG functions embedded in an IFN function works neatly:

 

data have;
input Date:mmddyy10. Condition Value;
format Date mmddyy10.;
datalines;
1/1/2005 1 5
1/2/2005 0 0
1/3/2005 0 0
1/4/2005 0 2
1/5/2005 0 3
1/6/2005 0 0
run;

data want;
  set have;
  value_lagged=ifn(lag(condition)=1 or lag2(condition)=1
                  ,max(lag(value),lag2(value))
                  ,0);
run;

 

This program assumes that for all records in which condition is not a 1, value is a zero.  Otherwise the MAX function might choose the wrong lagged value.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
ChrisNZ
Tourmaline | Level 20

Or:

data WANT ;
  set HAVE;
  VALUE_LAGGED=ifn(lag1(CONDITION), lag1(VALUE)
              ,ifn(lag2(CONDITION), lag2(VALUE)
              ,                     0          ));
run ;
mkeintz
PROC Star

@ChrisNZ's is an improvement over my suggestion, in that it handles consecutive CONDITION=1 cases more appropriately.  Namely it takes the most recent VALUE of the two qualifying records, not the maximum valuje.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 1371 views
  • 2 likes
  • 5 in conversation