How to make SAS recognize a user-defined format as a date?

[js5]

Hello,

I have defined the following format-informat pair:

proc format;
	invalue irtdate "N/A" = .a
		other = [date11.];
	value irtdate .a = "       N/A" 
		other = [e8601da10.];

Unfortunately, SAS does not recognize these as date formats. This causes the data to be uploaded to an SQL DB as float instead of datetime2. Is there a solution to this issue? Thanks!

[Patrick]

Look into data set option dbtype.

I've made - at least with earlier versions and some databases - the experience that SAS even doesn't always get it right with OOTB date and datetime formats. I therefore always try to apply formats date9. and datetime20. before uploading any SAS table to a database.

Now with your format: Not sure what conversion you would expect to happen for .a = " N/A"  even if using dbtype. Special missings are a SAS "thing" so not sure how SAS could convert this to a value for a DB. Also: You wouldn't expect to get "N/A" on the DB side, would you? What gets transferred to the database are the unformatted internal values.

IF you want to store the formatted values in the database then you would need to load a string - and it would become a CHAR or VARCHAR on the DB side. IF you want to load a string then use your format on the SAS side to populate a character variable and then load this character variable to the database.

[js5]

Regarding the intention: I am OK if the data are uploaded as NULL, I merely wanted to avoid having a ton of invalid data messages during the file import. I guess I can adjust the format to map N/A to . and then use standard date format for the dataset.

[Tom]

What code are you using to upload?

Can't you just include a FORMAT statement to attach DATE format to the variables for the upload step?

[js5]

I use

proc sql;
 create table odbc.foo as select * from saslib.foo;

I have now dropped my custom formats and adapted the informats to map N/A to . instead of .a. The values were uploaded as datetime2 so it looks like it worked.

[Tom]

So that is different than inserting into an existing table.

Using the DBTYPE= dataset option is probably recommended if you really want control over how the variables are defined in the remote database.  But it will require more code.

[Tom]

Did you try defining it as a picture format instead?  Does it recognize it a date value then?

proc format;
picture irtdate 
   .a = '       N/A' 
   .  = '         .'
  other = '%Y-%0m-%0d' (datatype=date)
;
run;

[js5]

I tried something similar, it did not work either.

[average_joe]

You don't give enough information to fully understand the problem, but I can see from the format you gave that it may cause a faulty data conversion to the SQL DB. 

If you are using SAS/ACCESS to <name your relational database> to interface with the DB, SAS makes an educated guess what data type to create on the DB-side based on the SAS data type and format. Since SAS only has 2 data types (numeric and character), it also uses the format to give additional clues for granularity. If you look at the documentation for SAS/ACCESS, it will tell you the formats it recognizes to properly create the SQL DB data type and perform the conversion.

You didn't specify which SQL DB you are working with, but I checked a couple of the ones I use mostly (Teradata and MS SQL Server) and the e8601da10. format is not listed as one of the formats for creating date data type on DB-side. You may want to try date. or datetime. Better yet click the link I provided and check out your specific DB.

[Tom]

At least for Redshift it just says "DATE formats".  It does not really list which formats it recognizes as "DATE formats".

I think the issue the OP was having is that is not seeing the user defined format as a "DATE format".

[average_joe]

As I mentioned, he doesn't give enough information to pinpoint the issue. You think it may be that the format is not applied to the SAS data, and, therefore, doesn't get properly created on the DB-side. I suggest that the format is properly applied but not recognized by the SAS/ACCESS engine to properly create it on the DB-side. My suggestion is based on Teradata stating that any other numeric format that is not listed results in a float.

Numeric	all other numeric formats	FLOAT

[js5]

I am using SAS/ACCESS to ODBC and loading data to MS SQL Server. As with Teradata, date formats should in theory be recognised as SQL_DATE. This works for e8601da. without issues, it is just my user-defined format with the additional missing values causing problems.

As mentioned earlier, the idea behind using it was to avoid excessive log spam due to N/A values. But, as I do not need the values after the import, I have adapted my informat as follows:

proc format;
	invalue irtdate "N/A" = .
		other = [date11.];

and formatted the values as e8601da afterwards. This has allowed me to both avoid the errors in the log and to load the data into the DB as a proper data type.

[Patrick · Accepted solution]

@js5 Because SAS doesn't have a data type of Date, Datetime, Time, Timestamp or similar the only way SAS can identify that a variable of type NUM (double) requires conversion to a Date/Time specific DB data type is via a format attached to the variable or via the DBTYPE option. 

If it's a SAS Format then it needs to be part of some internal list of OOTB formats as nothing within a custom format identifies it as a specific date, datetime or time format - so SAS simply can't "know" that a mapping to anything else than a DB type Double is required.