topic Re: difference between two dates by row per customer_id in SAS Programming

difference between two dates by row per customer_id

Ronein — Sat, 13 Jul 2019 07:33:42 GMT

Hello

I want to calculate the difference between two dates by row per customer_id.

Please find the raw data


data a;
  input CUSTOMER_ID date anydtdte23.;
  format date date9.;
cards;
1057086 21AUG2014
1057086 25AUG2014
1057086 17SEP2014
1057086 17SEP2014
1057086 19SEP2014
1057086 26SEP2014
9888221 01Feb2014
9888221 23Apr2014
9888221 23Apr2014
9888221 26May2014
9888221 27May2014
;
Run;

Re: difference between two dates by row per customer_id

Kurt_Bremser — Sat, 13 Jul 2019 08:18:39 GMT

Use the dif() function, and set the result to missing if first.customer_id.

Re: difference between two dates by row per customer_id

Ronein — Sat, 13 Jul 2019 11:35:21 GMT

Thank you.

For second customer 9888221 in first row I get -237 instead of null.

Why?

data a;
  input CUSTOMER_ID date anydtdte23.;
  format date date9.;
cards;
1057086 21AUG2014
1057086 25AUG2014
1057086 17SEP2014
1057086 17SEP2014
1057086 19SEP2014
1057086 26SEP2014
9888221 01Feb2014
9888221 23Apr2014
9888221 23Apr2014
9888221 26May2014
9888221 27May2014
;
Run;

proc sort data=a; by customer_id date; run;

Data b;
set a;
days_between = dif(date);
IF first.customer_id then days_between=.; 
Run;

Re: difference between two dates by row per customer_id

Ksharp — Sat, 13 Jul 2019 11:49:48 GMT

Data b;
set a;


by customer_id ;


days_between = dif(date);
IF first.customer_id then days_between=.; 
Run;

Re: difference between two dates by row per customer_id

Kurt_Bremser — Sat, 13 Jul 2019 15:10:45 GMT

first. and last. variables are only set if a proper by statement is present. Using a first. variable without a by causes a related message in the log.