topic Re: Convert date format: DATE9. to 'ddmmmyy'd in SAS Programming

Convert date format: DATE9. to 'ddmmmyy'd

FLY_FFM — Fri, 27 Mar 2020 10:16:40 GMT

Hi everyone,

I created this macro:

%macro date_loop(start,end);
%let start=%sysfunc(inputn(&start,anydtdte9.));
%let end=%sysfunc(inputn(&end,anydtdte9.));
%let dif=%sysfunc(intck(week.2,&start,&end));
%do i=0 %to &dif;
%let date=%sysfunc(intnx(week.2,&start,&i,b),date9.);
%put &date;
%end;

%mend date_loop;

%date_loop(29sep2019,30mar2020)

But in my skript I need to countinue with the following format: 'ddmmmyyyy'd

I tried to create the marco above in the format 'ddmmmyyyy'd, but it did not work. Has someone an idea how to change the format within the macro or afterwards?

Thank you!

Re: Convert date format: DATE9. to 'ddmmmyy'd

Kurt_Bremser — Fri, 27 Mar 2020 10:22:58 GMT

You do not need that complicated formatting. The raw value (count of days as integer) works just as well, and is much easier to handle.

If you need a human-readable form, use a proper format like yymmddd10. (which is the ISO-standard); if you need to use the value later as prompt value for a stored process, you only need the datae9. format without the quotes and the trailing "d".

How do you use the macro variables later in your code?

Re: Convert date format: DATE9. to 'ddmmmyy'd

DavePrinsloo — Fri, 27 Mar 2020 10:23:59 GMT

You probably want to use format ddmmyyn8.

Re: Convert date format: DATE9. to 'ddmmmyy'd

PaigeMiller — Fri, 27 Mar 2020 10:59:28 GMT

Normally, I would not repeat an answer given above, but this is important. Macro variables should not be formatted for any type of logical or arithmetic use.

Now I am going to repeat myself!! Macro variables should not be formatted for any type of logical or arithmetic use.

The only time you need macro variables to be formatted is when you are creating titles or labels or reports or when humans have to read the date. When SAS has to make use of the date, it should be unformatted.

%macro date_loop(start,end);
    /* Step 1: unformat the start and end dates */
	%let start=%sysevalf("&start"d);
	%let end=%sysevalf("&end"d);
	%put note: start and end are not formatted;
	%put &=start &=end;
	%put;
    /* arithmetic operations work on unformatted macro variables */
	%let dif=%sysfunc(intck(week.2,&start,&end));
	%do i=0 %to &dif;
		%let date=%sysfunc(intnx(week.2,&start,&i,b));
         /* write the results so we can see that the macro is working */
		%put &=i &=date;
		%put DATE FORMATTED %sysfunc(putn(&date,date7.));
	%end;
%mend date_loop;

%date_loop(29sep2019,30mar2020)

Re: Convert date format: DATE9. to 'ddmmmyy'd

Kurt_Bremser — Fri, 27 Mar 2020 10:27:31 GMT

Just to illustrate what I mean:

data have;
do date = today() - 3 to today() + 5;
  output;
end;
format date yymmddd10.;
run;

%let macvar1 = %sysfunc(today());
%let macvar2 = "%sysfunc(today(),date9.)"d;

%put &=macvar1 &=macvar2;

data want1;
set have;
where date = &macvar1;
run;

data want2;
set have;
where date = &macvar2;
run;

The two output datasets are identical.

Re: Convert date format: DATE9. to 'ddmmmyy'd

Kurt_Bremser — Fri, 27 Mar 2020 10:30:14 GMT

@PaigeMiller wrote:

The only time you need macro variables to be formatted is when you are creating titles or labels or reports.

And your CEO will NOT like dates in date9. format. They usually want ddmmyyp10. (German), MMDDYY10. (US), or DDMMYY10. (Australia, NZ, ...), or any other locally used date format.

Re: Convert date format: DATE9. to 'ddmmmyy'd

Tom — Sat, 28 Mar 2020 02:11:04 GMT

What did you try that did not work? Note that you probably do not want to use single quotes since those will stop the macro processor from working on the strings inside of them.

%let date="%sysfunc(intnx(week.2,&start,&i,b),date9.)"d;