SAS Code issue

[Talha_Ghaffar]

Hi,

I'm trying to get difference between two dates from my data set.

I have ID ,sequence and datetime columns in a table. I want to get difference between two rows having same ID.

Code and screenshot attached for your reference.

Highlighted records show be null or blank like row#1. Record # 10119 is perfect other records are getting issues.

[PaigeMiller]

Please post your code as text in your message, and not as an attachment. Please click on the "little running man" icon and past your code into the window that appears.

Please go back to your original post and change the title to a meaningful description of your problem. "SAS Code Issue" could be the title for EVERY post in this forum, and so is not meaningful.

[Reeza · Accepted solution]

data time_diff;
set final_history_tbl_seq;
by cust_obj_86_id;

prev_time = lag(change_time);
if not first.cust_obj_86_id then do;
time_diff_days = intck("dtday",change_time , prev_time );
time_diff_hour = intck("hour",change_time , prev_time );
end;

run;

Include your code in the post in the future and only the relevant section, we don't need the full program. 

You need to account for the first of each record and modify the lag() function to operation unconditionally. 

*%let ObjRk=10265;
%let ObjectNumber = 86;
%let alias = Incident;
%let user1Alias = user1;
%let user2Alias = user2;
%let user3Alias = user3;
%let user4Alias = user4;
%let user5Alias = user5;

*to exclude fields enter them in double quotes comma seperatred
%let fieldstoExclue = "field_1" , "Field_2";
%let fieldstoExclue = "Discoverylocation";

%let BObjectNm = CUST_OBJ_%sysfunc(trim(&ObjectNumber));
%let objTable = gcm.cust_obj_%sysfunc(trim(&ObjectNumber));
%let objtblRk = a.cust_obj_%sysfunc(trim(&ObjectNumber))_rk;
%let object_type_nm=customObject%sysfunc(trim(&ObjectNumber));

proc sql noprint;
				select list_nm into: list_nm
				from gcm.named_list_mapping_l
				where (field_nm) = "statusCd" and business_object_type_nm = "&object_type_nm";

				create table field_tbl_1 as
				select
					c.DESC_TXT as statusCd ,
					a.CHANGE_REASON_RK,
					a.VALID_FROM_DTTM,
					a.VALID_TO_DTTM,
					a.business_object_rk
				from
					gcm.CUST_FIELD_VALUE a
					inner join
					gcm.NAMED_LIST b
					on a.VALUE_CD=b.OPTION_CD and b.LIST_NM = "&list_nm"
					inner join
					gcm.BO_TRANS_L c
					on b.NAMED_LIST_RK = c.BUSINESS_OBJECT_RK and c.BUSINESS_OBJECT_NM = 'NAMED_LIST' and c.language_cd="DEF"

				where 
					a.BUSINESS_OBJECT_NM = "&BObjectNm"
					and
					a.CUST_FIELD_NM = "statusCd"
				order by a.business_object_rk, a.VALID_FROM_DTTM 
				;                      	
			quit;
			
			proc sql;
create table ChangeTracking as
select cust_obj_86_id,change_reason.change_reason_rk, reason_txt, usr.display_nm,
(save_dttm/1000.00 + 315601200) as change_time format=datetime16.
from gcm.change_reason
inner join gcm.cust_obj_86_l
on cust_obj_86_rk = business_object_rk
inner join gcm.orauser_l usr
on usr.user_rk = change_reason.user_rk
where business_object_nm = "CUST_OBJ_86"
order by cust_obj_86_id;

create table final_history as
select t1.*, ct.*
from field_tbl_1 t1
inner join ChangeTracking ct
on ct.change_reason_rk = t1.change_reason_rk
order by cust_obj_86_id, change_time;
quit;

proc sql;
create table final_history_l as
select f.cust_obj_86_id, f.display_nm ,f.change_time ,f.statusCd ,f.reason_txt
from final_history f
union 
select ct.cust_obj_86_id, ct.display_nm ,ct.change_time ,"Initial Save" as statusCd  ,ct.reason_txt
from ChangeTracking ct
where ct.reason_txt = "Initial Save";
run;

data final_history_tbl;
set final_history_l;
change_time = intnx('hour',change_time,8,'same');
run;

proc sort data=final_history_tbl;
by cust_obj_86_id change_time;
run;

data final_history_tbl_seq;

set final_history_tbl;
by cust_obj_86_id;
seq + 1;
if first.cust_obj_86_id then seq = 1;
run;

data time_diff;
set final_history_tbl_seq;

time_diff_days = intck("dtday",change_time , lag(change_time));
time_diff_hour = intck("hour",change_time , lag(change_time));

run;

/* data ChangeTracking_l; */
/* set ChangeTracking; */
/* month = put(change_time, monname.); */
/* year = year(change_time); */
/* run; */