Intck does not give the desired output

[lydiawawa]

Hi,

I'm trying to create a variable, days, that stores the amount of days from the minimum date extracted from the date variable on my dataset. I stored the min date to a macro variable, min_dt , and tried to use it in intck to calculate the number of days in comparing to the values in final_date:

proc sql;
select min(final_date) format=MMDDYY10.
  into :min_dt
  from master1;
quit;
%put &min_dt; /*min_dt is set to 04/11/2022*/

data master1_1;
set master1;
   days=intck('day', &min_dt, final_date);
run;

Somehow, I'm not getting the desired days. The output looks like this:

final_date	days
4/11/2022	22746
4/11/2022	22746
4/26/2022	22761
4/26/2022	22761
4/26/2022	22761
4/26/2022	22761
4/26/2022	22761
4/27/2022	22762

Final_date is in MMDDYY10

Anyone knows what the problem is?

[SASKiwi · Accepted solution]

You aren't specifying the macro date correctly. Try this:

proc sql;
select min(final_date) format=date9.
  into :min_dt
  from master1;
quit;
%put &min_dt; /*min_dt is set to 04/11/2022*/

data master1_1;
set master1;
   days=intck('day', "&min_dt"d, final_date);
run;

[andreas_lds]

Just don't use a format at all, when storing dates in macro variables, if you don't want to use them in titles, footnotes etc.

[Kurt_Bremser]

Maxim 28: Macro Variables Need No Formats.

Raw values are easier to use in code.

When your macro variable is resolved, you get this code:

data master1_1;
set master1;
   days=intck('day', 04/11/2022, final_date);
run;

4 divided by 11 divided by 2022 (this is the formula SAS sees) is a value very close to zero, so you get the count of days from the SAS zero point, which is 1960-01-01.

You also do not need INTCK when calculating a date difference in days, as SAS date values are integer counts of days.

proc sql;
select min(final_date)
  into :min_dt
  from master1
;
quit;

data master1_1;
set master1;
days = final_date - &min_dt.;
run;

But you can do this in one step:

data master1_1;
min_dt = '31dec9999'd;
do until (end1);
  set master1 end=end1;
  min_dt = min(min_dt,final_date);
end;
do until (end2);
  set master1 end=end2;
  days = final_date - min_dt;
  output;
end;
drop min_dt;
run;

or

proc sql;
create table master1_1 as
  select
    *,
    final_date - (select min(final_date) from master1) as days
  from master1
;
quit;

Codes untested, posted from my tablet.

[ballardw]

Maybe this bit of code will help understand exactly what happened.

I set the macro variable to your example "date" and use today() function to have a date to compare with.

%let min_dt=01/11/2022;

data example;
   x=  &min_dt.;
   days=intck('day', &min_dt., today());
   days2= intck('day',0,today());
   format days days2 date9.;
run;

Since you did not provide a proper date value but the formatted string 01/11/2022 then SAS interpreted that string as an instruction to divide 01 by 11 and then divide that result by 2022 giving a value much less than 1. So the "date" used by the INTCK function was essentially 0 as dates are not supposed to a fractional component and returned the date fo the second parameter.

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