topic Re: How do I apply squeeze macro in SAS Enterprise Guide

How do I apply squeeze macro

heleenw — Fri, 24 Sep 2021 08:40:40 GMT

Hi all,
I have EG 8 on a citrix environment and some slowliness is experienced which I thought I might try out the squeeze macro for.

But how do I apply it? When I export the code (written by others) that is over 30 pgs of code, where/how do I insert squeeze macro?

A code example like Code before applying squeeze versus Code after applying squeeze would help?

Any ideas are appreciated, not an urgent matter. But an important one I would say.
(For further performance enhancement I would have to probably do digging, deeper into others' codelines, eliminate unneeded columns/strangely written queries etc. but squeeze seemed to me maybe a quick win.)

Re: How do I apply squeeze macro

Kurt_Bremser — Fri, 24 Sep 2021 09:10:39 GMT

Just look at the start of the macro code (as downloaded from https://support.sas.com/kb/24/addl/fusion24805_1_squeeze2.html), it contains a description and a short usage example:

%macro SQUEEZE( DSNIN  /* name of input SAS dataset  */
              , DSNOUT /* name of output SAS dataset */
              ) ;

   /* PURPOSE: create SAS dataset &DSNOUT using LENGTH       */
   /*          statement for numeric vars that optimizes the */
   /*          variable to the fewest # of bytes needed      */
   /*          to maintain the accuracy of the values        */
   /*          contained in the variable                     */
   /*                                                        */
   /*          macro variable SQZLENTH is created which is   */
   /*          then invoked in a subsequent data step        */
   /*                                                        */
   /* NOTE:    only numeric variables are processed          */
   /*                                                        */
   /* EXAMPLE OF USE:                                        */
   /*          %SQUEEZE( DSNIN, DSNOUT )                     */

If you are talking about another macro, please post the top lines of the code, and/or the URL from where you got it.

Re: How do I apply squeeze macro

Patrick — Fri, 24 Sep 2021 09:21:01 GMT

The squeeze macro determines the max number of bytes required to store numerical values without loosing precision. The need to use such a macro is imho a very special and rather rare use case.

One would normally say: Know your data and define your columns lengths upfront as required. The "waste" is in the huge majority of cases with character variables and not with numerical ones. Changing the lengths of numerical variables is something you should be doing as an exception and only when you know exactly what you're doing and why.

If you've got performance issues then first thing to do is to run your code with options fullstimer; set. Then investigate the log to identify the longest running steps and spend your time tweaking these steps. Also worth is to examine your whole program and determine if you can optimize the flow by reducing passes through the data (=less data and proc steps) and reduce times you sort the data (SQL does a lot of implicit sorting). ....but where to look first should really be driven by identifying the longest running steps. You get often 80% of gain with 20% of effort just by tweaking the few most inefficient steps.

Re: How do I apply squeeze macro

Kurt_Bremser — Fri, 24 Sep 2021 09:23:33 GMT

Just to add to @Patrick 's comment:

Nowadays, when I inspect long-running programs (long-running in the context of our batch jobs means more than 5 minutes), I first look for steps that do a "lookup" (like finding the location of an insurance agent by sorting by agent code and joining with the agents table), where the sorts and joins can be replaced by using a hash object in a step that does other things. This alone had me cut down the overall execution time to a quarter in some places.