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?
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;
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;
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.
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.