converting character to date time-input error and format not displaying

[Caetreviop543]

I'm trying to covert a character variable, timestamp, to a datetime variable that will merge with another datetime variable, date_created, in the format datetime22.3 Timestamp is currently in this format:

And I would like it to match date_created, which looks like this (datetime22.3):

I tried input, but received the error "invalid argument to function input".

data want;
informal time datetime22.3;
set have;
time=input(timestamp, datetime22.3);
format time datetime22.3;
run;

Putting ?? before the format in the input statement got rid of the error and applied the format according to proc contents, but it produces no output.

data want;
informal time datetime22.3;
set have;
time=input(timestamp, ?? datetime22.3);
format time datetime22.3;
run;

The output looks like this, where the first column is the variable timestamp, and the second column is the variable time.

I appreciate any advice!

[SASKiwi · Accepted solution]

Try this:

data _null_;
timestamp = '2011-05-06 08:00:00';
time=input(timestamp, ymddttm22.);
format time datetime22.3;
put _all_;
run;

[Caetreviop543]

Thanks, that worked!

[ballardw]

The format of a variable has nothing to due with comparing values for "matching". The numeric values, if SAS date, time or datetime variables are used unless you apply the format with a PUT, in which case you are comparing something other than the value of the variable.

So you may have another issue because you show one picture with no fractional second values and the other with such (the .3 part of the format). So it is very likely you will get no matches and depending on exactly what you mean by "match" may have to round or truncate the value with the decimal portion.

[Caetreviop543]

Yes, I was worried about that. If I deleted the last three digits of seconds for date_created using substring, would I then be able to merge with Timestamp, assuming both had the same informat? As I understand, informat is how SAS reads the raw data, whereas format is how it is displayed. 

[ballardw]

SUBSTR in a very poor choice for dealing with numeric values.

If you use Round(variable,1) it will round a value to the nearest integer. Or Floor(variable) will truncate decimal portions to integer, or CEIL (variable) will increase a value to the nearest integer

[Tom]

---

@Caetreviop543 wrote:

Yes, I was worried about that. If I deleted the last three digits of seconds for date_created using substring, would I then be able to merge with Timestamp, assuming both had the same informat? As I understand, informat is how SAS reads the raw data, whereas format is how it is displayed. 

---

Probably not in this case. You will need additional code to convert the value the informat generates.   So to eliminate the fractions of second use INT() or ROUND(,1).  Or use INTNX() function to move the value to appropriate date/time/datetime boundary.  Or when comparing the values use some fuzz instead of just checking for equality.

The INFORMAT is more about how to interpret the strings.  So if you had a string like "10APR2020:09:50:00" you could use an informat of DATE9. read just the first 9 characters and create the number of days since 1960.  Or you could read it using the ANYDTDTM9. and read the same 9 characters and create the number of seconds since 1960 for the start of that date (ie the timepart would be zero).

Generally the informat does not constrain the decimal places being read.  Although you can limit the number of characters read by adjusting the informat width (be careful because when using LIST MODE input SAS will ignore the width of the informat specification and instead use the width of the word/token it is reading).  If you do specify a decimal as part of the informat then that is instructions for how to interpret strings that do not have a decimal point.  The decimal part of the informat tells SAS what power of ten to divide the integer the string presents by.  So it says how many places to move the implied decimal point.  If a decimal point is in the string already then that part of the informat specification is ignored.  So with 10.2 informat then string "123456" becomes the number 1,234.56 but the string "12.3456" becomes the number 12.3456 since it has a decimal point.  Notice that value is neither truncated or rounded to two decimal places.