Converting date to datetime character string

[A_SAS_Man]

I'm trying to automate some internal reporting that queries from a database, I'm doing this by having several macro variables that store dates. The goal is to have just one date that needs updated, and the rest of the formats automatically populate. In order to supply a usable date to our warehouse I need to go from this:

%let End_Date1 = 2021-09-01;

To this:

'09/01/2021 00:0:0'

In order to do this I've tried this so far.

%let End_Date1 = 2021-09-01;

%let End_Date_Alt= %sysfunc(inputn(&End_Date1., yymmdd10.),mmddyy8.);
%let End_Date_Time_Alt=%sysfunc(putn(&End_Date_Alt,mmddyy8.));

I was then going to try to concatenate End_Date_Time_Alt with the string " 00:0:0", but my attempted conversion from a date to a date represented as a character isn't working. Any help is appreciated.

[Tom · Accepted solution]

Do you want an actual DATETIME value (number of seconds since 1960)?

Or just that character string?  

Why does your example character string have inconsistency in the number of zeros used?

Do you need one version with two digit years? Or should both versions have four digit years.  (NOTE: Using only two digits for the year will cause confusion, remember the Y2K problem.)

If you are generating the string do you really need the quotes in the macro variable? Or just the text string? If you need the quotes do they have to be the single quotes? Or can they be double quote characters instead?

Try:

%let End_Date1 = 2021-09-01;

%let End_Date_Alt= %sysfunc(inputn(&End_Date1., yymmdd10.),mmddyy10.);
%let End_Date_Time_Alt=&End_Date_Alt 00:00:00;

[A_SAS_Man]

The character string inconsistency is dictated by the warehouse, it stores the data in that format unfortunately. So your solution worked well I just had to use 00:0:0 instead of what you had.

[ballardw]

You may have a lot better results by starting with which ever is your main "date" to be an actual date value, as in the numeric value that SAS uses to store dates. Then that value can be manipulated directly (more or less).

The reason your conversion fails is that you forced it to be in effect a random string of digits with the mmddyy8. format.

Is there something really crucial about having a mix of 2 digits for an hour value and single digits for minutes and seconds? That just plain looks odd to me. Instead of lots of ugly concatenation of macro variables I would suggest making your own datetime format since I have to assume that you will be doing this more than one time.

Proc format;
picture mydatetime
low-high ='%0m/%0d/%Y %0H:%M:%S' (datatype=datetime);
run;

%let End_Date1 = 2021-09-01;
%let End_Date_Alt= %sysfunc(inputn(&End_Date1., yymmdd10.));
%let End_Date_Time_Alt=%sysfunc(putn(%sysfunc(dhms(&end_date_alt.,0,0,0)),mydatetime.));

%put &End_Date_Time_Alt;

If you are going to make a lot of these variables I strongly suggest moving the code to a data step and using the CALL SYMPUTX statement to make the variables. The code will be much cleaner without having a bunch of %sysfunc calls. Plus once you have one date, time or datetime value you can use it with all of the functions and formats available.

And for almost all practical purposes the only time your code should use formatted values for dates, times or datetimes is in the stuff people read: Titles, Filenames perhaps, row and column headings in tables or values in reports. If you are using this to "match" existing values, then you need to fix your data to use actual date values, not random strings like 2019-09-01.

[A_SAS_Man]

In this instance I don't have much choice, I have to match the formatting to be able to pull data from the warehouse, and it uses that strange date time format. Fixing the data would be ideal, but out of my ability to control.