topic Re: Case when statement is not showing the correct output in New SAS User

Case when statement is not showing the correct output

Sandeep77 — Wed, 02 Aug 2023 14:26:03 GMT

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;

Re: Case when statement is not showing the correct output

ballardw — Wed, 02 Aug 2023 14:39:25 GMT

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;

Re: Case when statement is not showing the correct output

mkeintz — Wed, 02 Aug 2023 14:40:32 GMT

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.

Re: Case when statement is not showing the correct output

Tom — Wed, 02 Aug 2023 15:07:56 GMT

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.