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

Dear all, 

 

May you help me to find what is wrong with my statement below?

 

I tried to generated  the last Column with correct date  , however, the statement generated column " Line start date new " which is wrong at the highlight column. 

 

data test9;
set test8;
retain line_start_date_new;
if summerge=1 then line_start_date_new=lag(line_start_date);
else if missing(summerge) then line_start_date_new=.;
run;

 

Yesterday, I got help codes here, which generated correct results. But I do not understand how my codes are different from the help codes below. 😞 

 

data test9;
set test8;
retain line_start_date_new ;
line_start_date_new = ifc(summerge=1, lag(line_start_date), line_start_date_new);
if missing(summerge) then call missing(line_start_date_new);
run;

 

 

PatientIDLine start dateLine end datesummergeLine start date new ( wrong)Line start date new (correct)
A4/21/20154/24/20151. 
A8/12/20158/31/2015.. 
A11/5/201511/5/2015.. 
A1/12/20164/26/2016.. 
A6/7/20169/20/201614/21/20151/12/2016
A10/5/201611/2/201624/21/20151/12/2016
A1/31/20171/31/2017...
A5/4/20175/4/201716/7/20161/31/2017
A11/14/20171/10/201826/7/20161/31/2017
B1/16/20146/19/2014...
B7/10/20149/4/2014...
B10/9/20145/14/2015...
B6/8/20156/8/201515/4/201710/9/2014
B9/24/20159/24/2015...
B1/25/20163/7/2016...
B3/24/20168/25/201616/8/20151/25/2016
B10/14/20162/17/201726/8/20151/25/2016

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

LAG() does NOT return the value from the previous observation.  LAG() knows nothing about observations. It just remembers the values the variable had when you called it before.  By only running LAG() on some observations it cannot return the value you want since you never passed that value to it so it could remember it.  The IFN(), IFC() implementation work because SAS runs both the expressions every time the IFN() function runs.

 

It is just easier if you set the value into a variable first and then conditionally use the variable.

data test9;
  set test8;
  retain line_start_date_new;
  lag_line_start_date=lag(line_start_date);
  if summerge=1 then line_start_date_new=lag_line_start_date;
  else if missing(summerge) then line_start_date_new=.;
  drop lag_line_start_date ;
run;

 

View solution in original post

4 REPLIES 4
novinosrin
Tourmaline | Level 20

Conditional Lag is a concept that take sometime to master

 

Anyways, The code that uses IFC will work. But since your dates are numeric, use IFN instead of IFC.

 

To learn more about conditional lag, I think there is a tech paper floating in google authored by Mkeiintz or Howard schrier. 

VDD
Ammonite | Level 13 VDD
Ammonite | Level 13

this don't look correct

retain linestdt_new ;

should it be 

retain line_start_date_new ;

Tom
Super User Tom
Super User

LAG() does NOT return the value from the previous observation.  LAG() knows nothing about observations. It just remembers the values the variable had when you called it before.  By only running LAG() on some observations it cannot return the value you want since you never passed that value to it so it could remember it.  The IFN(), IFC() implementation work because SAS runs both the expressions every time the IFN() function runs.

 

It is just easier if you set the value into a variable first and then conditionally use the variable.

data test9;
  set test8;
  retain line_start_date_new;
  lag_line_start_date=lag(line_start_date);
  if summerge=1 then line_start_date_new=lag_line_start_date;
  else if missing(summerge) then line_start_date_new=.;
  drop lag_line_start_date ;
run;

 

Ivy
Quartz | Level 8 Ivy
Quartz | Level 8
Thank you for the explanation !

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 4 replies
  • 608 views
  • 0 likes
  • 4 in conversation