SAS Formats help to optimize sql queries? how

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

SAS Formats help to optimize sql queries? how

Hi,

 

Can any one answer my below question?

 

SAS Formats help to optimize sql queries or not ? how


Accepted Solutions
Solution
‎02-16-2016 01:36 PM
Trusted Advisor
Posts: 1,117

Re: SAS Formats help to optimize sql queries? how

Hi @Cherry,

 

Are you perhaps thinking of SQL queries to external databases?

 

In this case, there are even situations where using SAS formats can cause performance issues, namely with SQL queries which otherwise could be passed through to a non-SAS database. SAS-specific PUT(var, format.) syntax in the query would force such queries to be processed by SAS, hence require (possibly time-consuming) data transfers from the non-SAS database. The so-called unPUT technology (translating the PUT syntax into database SQL code) has been developed to optimize that type of queries in order to mitigate the performance issues. Here is an article about this subject: https://support.sas.com/resources/papers/sgf2008/optimization.pdf.

View solution in original post


All Replies
Contributor
Posts: 38

Re: SAS Formats help to optimize sql queries? how

Formats only effect how the data is displayed, and do not influence how it is stored or processed.
Super User
Posts: 11,343

Re: SAS Formats help to optimize sql queries? how

"Optimize" has several flavors: Time of program execution, use of disk space, time to program ( and related maintenance and code understanding).

 

So one might ask which bit you are trying to optimize?

 

I can see it helpful in the coding and maintenance stage if you have code looking for certain patterns of data. It may be that you have formats code MANY values, think something like ICD medical diagnosis, that you have formats to create categories.

It may be significantly easier to write code like

 

Where (putc(code,Format.) in ('This category' 'That category') instead of listing 60 or several 100 individule values. Also some coding systems change, like ICD-10 replacing ICD-9 where ICD-10 literally expanded many existing codes into a dozen or more sub-codes.

If the FORMAT had been changed then the changes in the underlying values are accounted for.

 

But again, "optimize" is somewhat situational so it may take some additional information.

Super User
Posts: 19,772

Re: SAS Formats help to optimize sql queries? how

PROC SQL doesn't recognize formats for Group By statements so I avoid anything with formats and Proc SQL, besides assigning them.

Solution
‎02-16-2016 01:36 PM
Trusted Advisor
Posts: 1,117

Re: SAS Formats help to optimize sql queries? how

Hi @Cherry,

 

Are you perhaps thinking of SQL queries to external databases?

 

In this case, there are even situations where using SAS formats can cause performance issues, namely with SQL queries which otherwise could be passed through to a non-SAS database. SAS-specific PUT(var, format.) syntax in the query would force such queries to be processed by SAS, hence require (possibly time-consuming) data transfers from the non-SAS database. The so-called unPUT technology (translating the PUT syntax into database SQL code) has been developed to optimize that type of queries in order to mitigate the performance issues. Here is an article about this subject: https://support.sas.com/resources/papers/sgf2008/optimization.pdf.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 333 views
  • 2 likes
  • 5 in conversation