topic Date difference using SAS INTCK in New SAS User

Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 05:40:52 GMT

Hi,

I am calculating difference in days between diagnosis date and first lab test date using intck, but my code is not working right. Two of my date variables are numeric in MMDDYYYY format. This is a longitudinal data and I need to retrieve the difference between these two dates based on the very first lab test right after the diagnosis date. So, I have two issues here, one I am not sure how to use the intck function correctly and secondly how to select only the first labdate right after the diagnosis date. I have been asked to use only intck function, please keep in mind. This is a big data set with millions of records.

This is my code and this is what I get:

Thanks,

Re: Date difference using SAS INTCK

ballardw — Wed, 21 Jul 2021 06:26:29 GMT

Does your log say anything about conversion of character to numeric values? You might see something like:

NOTE: Character values have been converted to numeric values at the places given by:
      (Line):(Column).

If so you do not have date variables at all, you have character variables that need to be turned into actual SAS values before using the INTCK function.

Or do you get something like

NOTE: Invalid argument to function INTCK('day',7152021,7212021) at line XX column YY.

That will happen you have simple numeric values that look like 07152021. They are also not date values.

We need to see which actual values you have to recommend which code is needed to create date values.

Examples converting character or numeric values that look like you say they might:

  data junk;
     x='07152021';
     y=7212021;
     /* character value*/
     newx=input(x,mmddyy10.);
     /*numeric value*/
     newy=input(put(y,z8.),mmddyy10.);
     format newx newy mmddyy10.;
     z = intck('day',newx,newy);
  run;

Please save yourself some trouble and don't make pictures of code or log results. Copy text from the editor or log and on the forum open a text box using the </> icon that appears above the message window. Then paste the code.

It is much easier to point out needed changes to code when it is text. If there are enough issues with code some of us will not take the time to retype log sections to make multiple minor changes.

Also, read the log. Really, it is very helpful. If you see something in the log you don't understand, copy the code and the messages from the log and paste into text box. The text box is important because the main message windows will reformat text and may mean that some of the diagnostics that SAS supplies will not appear correctly.

When unexpected results, such as a bunch of missing values are generated one of the first things is to make sure that the variables and the values you are using are appropriate for the function. In the case of date related functions you need 1) numeric variables and 2) in specific ranges. SAS dates are the number of days since 1 Jan 1960. So if you have a number like 07152021 that would the year 21,705. For some likely very good reasons none of the SAS date functions work with dates past year 20,000. Proc contents will help. If the variable type is character it is pretty obvious what needs to be done. If the type is numeric but the format is something like BEST12. or F12. then you have plain number and seldom will it resolve with date functions.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/ta-p/424354 has a PDF with much information about dates.

It is best to p

Re: Date difference using SAS INTCK

andreas_lds — Wed, 21 Jul 2021 06:35:22 GMT

Problem: Your data step has no set-statement.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 11:19:22 GMT

Thank you, here is the log when I run this code. My dates are numeric in MMDDYYY format like 02/05/2019. Should I change it to sas dates and remove the missing dates as the log says?

data hiv;
no_of_days = intck ('DAY',hivdiag_date,labdate);
run;

NOTE: Variable hivdiag_date is uninitialized.
NOTE: Variable labdate is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
Each place is given by: (Number of times) at (Line):(Column).
1 at 51:14
NOTE: The data set WORK.HIV has 1 observations and 3 variables.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 11:24:40 GMT

Re: Date difference using SAS INTCK

Kurt_Bremser — Wed, 21 Jul 2021 11:27:10 GMT

Because of the missing SET statement, you have no "incoming" variables, so they are created on the fly and set to missing.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 11:43:08 GMT

data hiv;
no_of_days = intck ('DAY',hivdiag_date,labdate);
run;

NOTE: Variable hivdiag_date is uninitialized.
NOTE: Variable labdate is uninitialized.
NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      1 at 51:14
NOTE: The data set WORK.HIV has 1 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.08 seconds
      cpu time            0.01 seconds

Re: Date difference using SAS INTCK

PaigeMiller — Wed, 21 Jul 2021 11:57:40 GMT

@sas_sad

Two people have pointed out you have no SET statement, so your variables hivdiag_date and labdate don't have values. You need to fix that.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 12:01:04 GMT

I used the set statement but still gives the same result.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 12:03:57 GMT

I did, still gives the same result. Does not give any result for the difference in days.

Re: Date difference using SAS INTCK

PaigeMiller — Wed, 21 Jul 2021 12:14:56 GMT

@sas_sad wrote:
I used the set statement but still gives the same result.

Okay, @sas_sad , here's an important concept. When you have code that doesn't work, SHOW US the LOG so we can see the code you used, as it appears in the LOG, plus all ERRORs, WARNINGs and NOTEs. Do not pick and choose parts of the log to show us, show us ALL of it, every single character of the LOG, for the code in question. (This should be done every single time from now on, without us asking to see the LOG)

Another important concept: If you tell us something doesn't work, and provide no other information, we cannot help you.

Re: Date difference using SAS INTCK

Tom — Wed, 21 Jul 2021 12:14:02 GMT

@sas_sad wrote:
I did, still gives the same result. Does not give any result for the difference in days.

But you haven't shown the log from any runs that actually used any data.

Note that if your variables are dates (the number of days since 1960) then you can get the difference in days by simple subtraction.

data want;
  set have;
  diff = date1 - date2 ;
run;

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 12:20:03 GMT

data hiv1;
set hiv;
no_of_days = intck ('DAY',hivdiag_date,labdate);
run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      2603 at 60:14
NOTE: There were 2995385 observations read from the data set WORK.HIV.
NOTE: The data set WORK.HIV1 has 2995385 observations and 364 variables.
NOTE: DATA statement used (Total process time):
      real time           21.77 seconds
      cpu time            0.85 seconds

Re: Date difference using SAS INTCK

Tom — Wed, 21 Jul 2021 12:28:51 GMT

@sas_sad wrote:

data hiv1;
set hiv;
no_of_days = intck ('DAY',hivdiag_date,labdate);
run;

NOTE: Missing values were generated as a result of performing an operation on missing values.
      Each place is given by: (Number of times) at (Line):(Column).
      2603 at 60:14
NOTE: There were 2995385 observations read from the data set WORK.HIV.
NOTE: The data set WORK.HIV1 has 2995385 observations and 364 variables.
NOTE: DATA statement used (Total process time):
      real time           21.77 seconds
      cpu time            0.85 seconds

So you got it to work.

The note is saying that in 2,603 observations out of your 2,995,385 total observations one or more of your dates was missing.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 12:28:59 GMT

I posted my log above, I used the set statement, still nothing.

Re: Date difference using SAS INTCK

sas_sad — Wed, 21 Jul 2021 12:30:45 GMT

No, but it does not give me difference in days between diagnosis date and lab test date.

Re: Date difference using SAS INTCK

Tom — Wed, 21 Jul 2021 12:40:38 GMT

@sas_sad wrote:
No, but it does not give me difference in days between diagnosis date and lab test date.

How can you tell? What do you get it you use PROC MEANS to look a the min/max/mean of your new calculated variable?

Look at your original dataset. How are those two "date" variables defined.

Since you didn't get any message about converting characters to number then the two variable are numeric. But what types of values do they contain. SAS stores dates as the number of days since 1960. To make the number look readable to humans you need to attach for format like DATE or YYMMDD to the variable.

Some people create "date" variables that actual contain a datetime (or timestamp) value instead. SAS stores those as the number of seconds since 1960. If you want to calculate the number of days between two datetime values with INTCK() you need to use the DTDAY interval and not the DAY interval. If one is DATE and the other is DATETIME then you need to convert one of them before comparing them.

Sometime people also create numeric variables with integer values like 20,210,721 and think those are date because they look like YYYYMMDD values when printed.

Re: Date difference using SAS INTCK

PaigeMiller — Wed, 21 Jul 2021 12:46:28 GMT

@sas_sad wrote:
I posted my log above, I used the set statement, still nothing.

As before, saying it doesn't work and NOT EXPLAINING doesn't help us and doesn't help us help you.

What do you mean by "still nothing". Can you SHOW US what you see that indicates "still nothing"?