topic Re: SAS/ACCESS: Remap default data types and SAS formats in SAS Data Management

SAS/ACCESS: Remap default data types and SAS formats

LinusH — Thu, 08 Aug 2019 11:06:41 GMT

Maybe I missed something obvious, but here goes.

For each SAS/ACCESS engine, there is a table in the documentation that descibes the default mappings from the source (database) data type and the format it will have in SAS.

Is there a way to affect this mapping, on a library level?

I know that there is a DBSASTYPE= data set option, but that acts on individula columns. A bit awkward if you like this to be applied to a schema with potentially hundreds of columns of a certain data type.

My current example is that I want date to use YYMMDD10., not DATE9..

Re: SAS/ACCESS: Remap default data types and SAS formats

JBailey — Thu, 08 Aug 2019 13:32:35 GMT

Hi @LinusH,

This is an exciting idea. Right now, there is no way to remap an entire LIBRARY in one fell swoop. I am going to create a feature request for your idea. I am thinking that a SAS data set containing the conversion details would be an excellent way to approach this. It is possible to remap SAS functions using a similar technique.

Thanks for posting this,

Jeff

Re: SAS/ACCESS: Remap default data types and SAS formats

JackHamilton — Thu, 08 Aug 2019 13:56:43 GMT

An automatic remapping feature would be very useful.

Our use case: we have a SAS copy and a database copy of our data warehouse. We would like to be able to run the same code, unchanged except for libname statements, against either copy.

When our database was Teradata we could do that. But we have been switched to Oracle.

The problem is that SAS and Teradata have both date and datetime data types, but Oracle has only a datetime type. SAS/Access will convert dates to datetimes when sending a query to Oracle, but does not convert datetimes to dates when returning results. That breaks programs written to expect date values.

The SASDATEFMT data set option could at one time be set through an environment variable, which might solve the problem, but I think that's now deprecated.

Your suggestion would be a more flexible way to do that (I would want to change both the format and the data type, which is what sasdatefmt does, instead of only the format).

Re: SAS/ACCESS: Remap default data types and SAS formats

LinusH — Thu, 08 Aug 2019 15:08:21 GMT

Either that, or a Libname option similar to DBSASTYPE, like from data type to SAS format...

Thanks for the swift response!

Re: SAS/ACCESS: Remap default data types and SAS formats

JackHamilton — Thu, 08 Aug 2019 19:09:11 GMT

There are lots of ways that SAS might do it. Another way, a bit clumsier but with other benefits, would be to allow FedSQL views to be used in base SAS; it seems to do the conversion automatically if you have declared data types correctly, but unfortunately FedSQLs are broken outside FedSQL.

I was thinking that a list in a libname (SASDATEFMT or DBSASTYPE) would be the way to go, but the suggestion of having the translation list stored in a data set is a good one. It would allow you to specify different data types for the same variable name in different data sets.

And if you think "No one would be so careless as to declare the same variable name in different ways in different data sets in the same library", haha, you haven't met our vendors.

Re: SAS/ACCESS: Remap default data types and SAS formats

ballardw — Thu, 08 Aug 2019 22:05:58 GMT

@JackHamilton wrote:

And if you think "No one would be so careless as to declare the same variable name in different ways in different data sets in the same library", haha, you haven't met our vendors.

You will not get any push back from me on that point. I won't mention names but perhaps we have some of the same vendors...