BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Sandeep77
Lapis Lazuli | Level 10

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;
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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;

 

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Jade | Level 19

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Tom
Super User Tom
Super User

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.

 

 

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

From SAS Users blog
Want more? Visit our blog for more articles like these.
5 Steps to Your First Analytics Project Using SAS

For SAS newbies, this video is a great way to get started. James Harroun walks through the process using SAS Studio for SAS OnDemand for Academics, but the same steps apply to any analytics project.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 744 views
  • 1 like
  • 4 in conversation