SAS vs SQL Update

I have always preferred to use SQL versus SAS syntax because of the wider usage of SQL for general data manipulation. However, I have heard that the SAS update statement is much more efficient than SQL for updates, so I wrote a test case to verify.

Sure enough, the SAS update statement is several orders of magnitude faster when updating roughly 5,000 rows from one table to a 100,000 row table.

data sample ;
  do id = 1 to 100000 ;
    lname = 'Smith' ;
    output ;
  end ;
run ;

data upd ;
  set sample ;
    if ranuni( 1 ) <= .05 then do ;
      lname = 'Jones' ;
      output ;
    end ;
run ;

data sample ;
  update sample upd  ;
    by id ;
run ;

proc sql ;
  update sample s
    set lname = 
      ( select   lname 
          from   upd u
          where  u.id = s.id 
      )  
      where id in
      ( select   id
          from   upd u 
      ) ;
quit ;

proc sql ;
  update sample s
    set lname = 
      ( select   lname 
          from   upd u
          where  u.id = s.id 
      )  
      where exists
      ( select   * 
          from   upd u 
          where  u.id = s.id 
      ) ; 
quit ;

See also: Update Statement, Updating a Table with Values from Another Table

What the HEX. is going on?

Have you ever had to compare two values that appear to be the same but they are different? That is likely the result of the way data is stored using floating-point representation.

A great way reveal differences is to use the HEX16. format. See the below example and notice that the right most value between the x1hex and x2hex columns are different. The issue can be resolved by using the ROUND() function to mitigate the floating-point difference.

data x ;
  x1 = .3 ;
  x2 = 3 * .1 ;
  xcomp = x1 - x2 ;
  x1hex = put( x1, hex16. ) ;
  x2hex = put( x2, hex16. ) ;
  r1 = round( x1, .00000001 ) ;
  r2 = round( x2, .00000001 ) ;
  rcomp = r1 - r2 ;
run ;

For more information on numerical accuracy in SAS software, see this link.

ODS Styled Brochure

The below code shows how you can create a SAS ODS PDF styled brochure. Picture formats are used along with the dynamic putn() function to display the numeric attributes of the Audi R8. The source code reveals how to use ODS TEXT = with various ~{style}s. Proc report headers and borders are suppressed via rules = none and frame = void settings. Here is a link to the generated PDF file

proc format ;
   picture engine low - high = "099" ( prefix = 'v' ) ;
   picture mph    low - high = "999 mph" ;
   picture lbs    low - high = "9,999 (lb)" ;
   picture sec    low - high = "09.9 sec." ( mult = 10 ) ;
run ;

data x ;
  input name & $32. val format $ ;
  datalines ;
  Engine  10 engine.
  Horsepower  550 comma.-l
  0 to 60 mph  3.3 sec.
  Top Speed  197 mph.
  Price as Configured  198800 dollar9.
  Curb Weight  3660 lbs.
  ;
run ;

data r8( keep = bullet name value ) ;
  bullet = "6c"x ;
  set x ;
  value = putn( val, format ) ;
run ;

options
  nodate
  nonumber
  orientation = portrait ;

ods escapechar = '~'  
  noresults  
  noproctitle ;
title ;
footnote ;

ods listing close ;
ods pdf
  file         = "c:\temp\audir8.pdf"
  author       = "&sysuserid."
  dpi          = 300
  subject      = "Audi R8"
  notoc
  bookmarklist = hide
  startpage    = no ;

  ods text = "~{style
    [ preimage = 'c:\temp\auditruth.jpg'
      fontsize = 28pt
      fontweight = bold]          2015 Audi R8}" ;
  ods text = "~{style
    [ outputwidth = 100%
      borderbottomcolor = red
      borderbottomwidth = 1pt ]}" ;
  ods text = "~2n" ;
  ods text = "~{style
    [ preimage = 'c:\temp\audir8blue.jpg' 
      just     = center ]}" ;

  proc report
    data = r8
    nowd
    noheader
    style( report ) = [ rules = none frame = void ] ;

    column bullet name value ;
    define bullet / style( column ) = [ font_face = wingdings just = c ] ;
    define name   / style( column ) = [ font_face = arial font_size = 18pt ] ;
    define value  / style( column ) = [ font_face = arial font_size = 18pt ] ;
  run ;

ods pdf close ;
ods listing ;