topic Re: Difference between 2 rows in SAS Programming

Difference between 2 rows

mohtamimi94 — Thu, 17 Feb 2022 08:32:49 GMT

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

Re: Difference between 2 rows

Kurt_Bremser — Thu, 17 Feb 2022 08:59:54 GMT

"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.

Re: Difference between 2 rows

mohtamimi94 — Thu, 17 Feb 2022 11:08:49 GMT

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

Re: Difference between 2 rows

Kurt_Bremser — Thu, 17 Feb 2022 12:02:32 GMT

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..

Re: Difference between 2 rows

mohtamimi94 — Thu, 17 Feb 2022 14:12:03 GMT

Thank you very much