topic Re: SAS date and data steps in SAS Programming

SAS date and data steps

goldenone — Thu, 23 Jun 2022 23:34:25 GMT

Hello, I am trying to use the first of the month as the date for a dataset. however, all I am able to come up with is the actual SAS day since Jan 1960. For example, since we are now as of time of writing in June 2022, I would want 010522 NOT 23767.

%let offmnth = %sysfunc(intnx(month, %sysfunc(today()), -1),ddmmmyy.);


data  test&offmnth;
   a= &offmnth;
   put 'SAS date='a;
    put 'formatted date='a date9.;
	run;

Re: SAS date and data steps

Patrick — Fri, 24 Jun 2022 00:20:33 GMT

I find it often easier to debug one of these nested %sysfunc() expressions using a SAS data step.

data test;
  s1=today();
  s2=intnx('month',today(),-1,'b');
  s3=put(intnx('month',today(),-1,'b'),ddmmmyy.);
run;
proc print data=test;
run;

In doing so you'd easily spot that you're using a wrong format name.

If you fix the format then you'll get what you're after

%let offmnth = %sysfunc(intnx(month, %sysfunc(today()), -1,b),ddmmyyn.);
%put &=offmnth;

If this is just about creating some macro variables then you could also just stick with the data _null_ step because it's imho easier to develop, read and maintain.

data _null_;
  dt=intnx('month',today(),-1,'b');
  call symputx('offmnth',put(dt,ddmmyyn.));
run;

Re: SAS date and data steps

Tom — Fri, 24 Jun 2022 02:23:15 GMT

@goldenone wrote:

Hello, I am trying to use the first of the month as the date for a dataset. however, all I am able to come up with is the actual SAS day since Jan 1960. For example, since we are now as of time of writing in June 2022, I would want 010522 NOT 23767.
%let offmnth = %sysfunc(intnx(month, %sysfunc(today()), -1),ddmmmyy.);


data  test&offmnth;
   a= &offmnth;
   put 'SAS date='a;
    put 'formatted date='a date9.;
	run;

Your %SYSFUNC() call is not using a valid FORMAT, there is no format named DDMMMYY. If you want print a date in Day Month Year order use the DDMMYY format. If you want it without any separator use the DDMMYYN format.

Note that the number 23,767 is the actual DATE since SAS records dates as number of days since 1960. The number 10,522 is NOT a DATE value. And what date value would that number even mean? Is that Oct 5, 1922? Oct 5 2022? May 22nd, 1910? Jan 1st, 2022? May 1st, 1922?

If you need to use the value as a date then store it as a date value. Then you can compare it with other dates, sort it, group it. If you have it print in a style that humans will recognize then use a FORMAT. But do not use DDMMYY strings (or worse number in DDM,MYY style) to store dates. It will cause confusion.

If you use either DMY or DMY order you will confuse half of your audience. (and if you use only two digits for the year make that ALL of your audience). Use either the DATE or YYMMDD format to avoid confusion.

Plus if you use that digit string as part of a DATASET name then you really want to use YMD order so that the filenames will sort into chronological order.

%let offmnth = %sysfunc(intnx(month, %sysfunc(today()), -1),yymmddn8.);

data test&offmnth;
   date = input("&offmnth",yymmdd8.);
   format date date9.;
   put "Digits are &offmnth..  Raw date is: " date :comma7.  date= ;
run;

Digits are 20220501.  Raw date is: 22,766 date=01MAY2022

Re: SAS date and data steps

Kurt_Bremser — Fri, 24 Jun 2022 04:04:58 GMT

When time-stamping files, always use a YMD order; it is much better to handle (files sort automatically, and periods are easy found with wildcards).

Always use 4-digit years for formatted dates.

Re-convert the formatted macro variable with the INPUT fuhction.

Assign a format in the data step.

%let offmnth = %sysfunc(intnx(month, %sysfunc(today()), -1, b),yymmddn8.);

data  test&offmnth.;
a = input("&offmnth.",yymmdd8.);
format a yymmdd10.;
put 'SAS date='a 5.;
put 'formatted date='a date9.;
run;

Re: SAS date and data steps

PaigeMiller — Fri, 24 Jun 2022 11:26:38 GMT

Do NOT format macro variables that contain dates (unless you want to use them in titles or labels. which does not appear to be the case here as you want to use this macro variable in a data step). See Maxim 28.

%let offmnth = %sysfunc(intnx(month, %sysfunc(today()), -1));
data  test&offmnth;
   a= &offmnth;
   put 'SAS date=' a;
   put 'formatted date=' a date9.;
   put 'formatted date=' a ddmmyyn6.;
run;

For example, since we are now as of time of writing in June 2022, I would want 010522 NOT 23767.

Unless you have some strict requirement, do not use 2 digit years, use 4 digit years, in which case use format ddmmyyn8.

SAS Dates are recorded in the number of days since 01JAN1960, and so you DO want 23767, which will work properly when you perform any aritmetic or logical operations in the DATA step, and can be made to appear as an actual date by using a format.