Difference between 2 rows

[mohtamimi94]

Dears,

We have a configuration on the system to send a survey for the customers who contacted us but the rule is to not send the survey twice in a week.

For example, if X contacted me on the 1st of Jan then he will receive the survey but if he called us again on the 3rd of Jan then he will not receive the survey. If he contacted us again on the 9th of Jan then he shall receive the survey again.

I have the calls log but I want to validate if this call shall receive the survey or not.

I tried to write a code on SAS as below but it's not working:

data want;
set want;
by caller_number;
format previous_date DATEAMPM19.;
format previous_number $32.;
format last_survey_sent DATEAMPM19.;
format repeated $32.;
repeated = "Yes";
previous_number=lag(caller_number);
previous_date=lag(DATETIME);
if first.id then last_survey_sent = lag(DATETIME);
if first.id then call missing(previous_date,previous_number);
if previous_number ^= caller_number then call missing(previous_date,previous_number);
if DATETIME - previous_date > 604800 OR missing(previous_date) then call missing(repeated);
run;

It calculates the difference between 2 rows if it's greater than 1 week or not

Thanks in advance

[Kurt_Bremser]

"It's not working" without a detailed description of what went wrong is not helpful.

Post the log (especially if extraneous NOTEs or WARNINGs/ERRORs occur), and describe where the results did not meet your expectations.

Also post example data (in a data step with datalines, do not skip this!), so we can test code against what you have.

[mohtamimi94]

Hello,

Sorry for the incomplete information, kindly find the below sample of source data:

DATETIME                              CALLER_NUMBER
01JAN2022:14:37:35              770000121

03JAN2022:17:12:23              770000121

08JAN2022:11:09:51              770000121

07JAN2022:22:08:46              791234567

08JAN2022:14:36:54              791234567

01JAN2022:10:09:01              799876543

Sample Output:

DATETIME                        CALLER_NUMBER      previous_date                           previous_number          last_survey_sent          repeated
01JAN22:02:37:35 PM      770000121
03JAN22:05:12:23 PM      770000121                    01JAN22:02:37:35 PM              770000121                                                        Yes
08JAN22:11:09:51 AM       770000121                   03JAN22:05:12:23 PM              770000121                                                        Yes
07JAN22:10:08:46 PM       791234567 
08JAN22:02:36:54 PM       791234567                   07JAN22:10:08:46 PM              791234567                                                        Yes
01JAN22:10:09:01 AM       799876543

While the results shall be as below:

DATETIME                        CALLER_NUMBER      previous_date                           previous_number          last_survey_sent               repeated
01JAN22:02:37:35 PM      770000121                                                                                                          01JAN22:02:37:35 PM
03JAN22:05:12:23 PM      770000121                    01JAN22:02:37:35 PM              770000121                  01JAN22:02:37:35 PM      Yes
08JAN22:11:09:51 AM       770000121                   03JAN22:05:12:23 PM              770000121                   08JAN22:11:09:51 AM                                        
07JAN22:10:08:46 PM       791234567                                                                                                          07JAN22:10:08:46 PM 
08JAN22:02:36:54 PM       791234567                   07JAN22:10:08:46 PM              791234567                   07JAN22:10:08:46 PM      Yes
01JAN22:10:09:01 AM       799876543                                                                                                           01JAN22:10:09:01 AM

Thanks in advance

[Kurt_Bremser · Accepted solution]

See this code to identify when a survey should be sent:

data have;
input datetime : datetime19. caller_number :$15.;
format datetime e8601dt19.;
datalines;
01JAN2022:14:37:35              770000121
03JAN2022:17:12:23              770000121
08JAN2022:11:09:51              770000121
07JAN2022:22:08:46              791234567
08JAN2022:14:36:54              791234567
01JAN2022:10:09:01              799876543
;

data want;
set have;
by caller_number;
retain date_survey;
format date_survey e8601dt19.;
if first.caller_number
then do;
  date_survey = datetime;
  survey = "yes";
end;
else do;
  if intck('dtday',date_survey,datetime) > 6
  then do;
    survey = "yes";
    date_survey = datetime;
  end;
end;
run;

Note how the initial data is presented in a data step with datalines; you will do so in the future so we don't have to do it for you, making guesses along the way about content and attributes..

[mohtamimi94]

Thank you very much