Case when statement is not showing the correct output

[Sandeep77]

Hi all,

I am running a code and used intck to find the dates which are more than 6 months and which are less than 6 months using case when statement. My code runs fine but in the output it shows all data as less than six months even if it is there for more than six months. Can you please suggest the mistake in the code? Thanks

I want the output data to show accounts with more than 6 months from EntryDate  and which are less than 6 months from EntryDate.

Sample dataset

Data Status;
infile cards expandtabs;
input rep_group $ Serial client_code $ EntryDate :date9. Entrydatestatus$;
datalines ;
742 441844248 SHT1064 28JUL2023:00:00:00.000 Lessthansixmonths
100 442507166 PAY023 01AUG2022:00:00:00.000 Lessthansixmonths
125 442552022 PAY023 01AUG2023:00:00:00.000 Lessthansixmonths
100 442034021 EE07 28JUL2022:00:00:00.000 Lessthansixmonths
521 443717608 KLAA004 01AUG2023:00:00:00.000 Lessthansixmonths
;
run;

proc sql;
create table Status AS
Select rep_group,
Serial,
client_code,
EntryDate,
case when intck('month', EntryDate, date()) >= 6 then '6 or more months'
when intck('month', EntryDate, date()) < 6 then 'Less than 6 months'
end as EntryDate_status
From p2scflow.debt
Where rep_code LIKE '1%'
Group By rep_group;
quit;

[ballardw · Accepted solution]

I think you need to provide example data from p2scflow.debt, and possibly output from proc contents describing the variables used.

The fact that you show datetime values and truncate by reading with with a Date9 format for your EntryDate variable is a bit suspect. If your actual Entrydate is a datetime then the likely range of values exceeds the interval "month", which would treat EntryDate as a date value, expects and so you actually get missing values for the interval, which do indeed get assigned to "less than 6" as missing is always less than any value.

Rerun your SQL and just get the interval returned by the INTCK function instead of that case and look.

If you get all missing then try

 intck('month', datepart( EntryDate), date())

---

@Sandeep77 wrote:

Hi all,

I am running a code and used intck to find the dates which are more than 6 months and which are less than 6 months using case when statement. My code runs fine but in the output it shows all data as less than six months even if it is there for more than six months. Can you please suggest the mistake in the code? Thanks

I want the output data to show accounts with more than 6 months from EntryDate  and which are less than 6 months from EntryDate.

Sample dataset

Data Status;
infile cards expandtabs;
input rep_group $ Serial client_code $ EntryDate :date9. Entrydatestatus$;
datalines ;
742 441844248 SHT1064 28JUL2023:00:00:00.000 Lessthansixmonths
100 442507166 PAY023 01AUG2022:00:00:00.000 Lessthansixmonths
125 442552022 PAY023 01AUG2023:00:00:00.000 Lessthansixmonths
100 442034021 EE07 28JUL2022:00:00:00.000 Lessthansixmonths
521 443717608 KLAA004 01AUG2023:00:00:00.000 Lessthansixmonths
;
run;

proc sql;
create table Status AS
Select rep_group,
Serial,
client_code,
EntryDate,
case when intck('month', EntryDate, date()) >= 6 then '6 or more months'
when intck('month', EntryDate, date()) < 6 then 'Less than 6 months'
end as EntryDate_status
From p2scflow.debt
Where rep_code LIKE '1%'
Group By rep_group;
quit;

---

[mkeintz]

Apply your SQL code to the sample data, I cannot reproduce the problem.  I.E., the code:

Data Status;
infile cards expandtabs;
input rep_group $ Serial client_code $ EntryDate :date9. Entrydatestatus$;
   format entrydate date9. ;
datalines ;
742 441844248 SHT1064 28JUL2023:00:00:00.000 Lessthansixmonths
100 442507166 PAY023 01AUG2022:00:00:00.000 Lessthansixmonths
125 442552022 PAY023 01AUG2023:00:00:00.000 Lessthansixmonths
100 442034021 EE07 28JUL2022:00:00:00.000 Lessthansixmonths
521 443717608 KLAA004 01AUG2023:00:00:00.000 Lessthansixmonths
;
run;

proc sql;
  create table new_Status AS
  Select rep_group, Serial,  client_code,  EntryDate,
   case 
     when intck('month', EntryDate, date()) >= 6 then '6 or more months'
     when intck('month', EntryDate, date()) < 6 then 'Less than 6 months'
   end as EntryDate_status
   From status      /*p2scflow.debt*/
   /*Where rep_code LIKE '1%'*/
   Group By rep_group;
quit;

yields "6 or more" for the first two, and "less than 6" for the last three obs.

Perhaps your WHERE subset, which I had to comment-out, may be filtering out all the 6-months-or-more rows.

[Tom]

INTCK() is counting interval boundaries between the start value and the target value.  When the START  value is AFTER the TARGET value then the answer is negative.

Looks like you gave INTCK() a datetime value (number of seconds) for the start value and a date value (number of says) as the target value.  That is always going to be negative since treating a count of seconds as a count of days is going to result in some date that is way way way in the future since there are 24*60*60 seconds in a day.