xtab using proc report and tabulate

Here is an example to transpose data to a cross tab report using proc report and proc tabulate.

data input ;
  do _n_ = 26 to 31 ;
    do year = 2009 to 2012 ;
      date = put( mdy( 12, _n_, 2012 ), yymmdd10. ) ;
      value = round( ranuni( 1 ) * 1000, .01 ) ;
      output ;
    end ;
  end ;
run ;

The below image contains the results of both procedures. The top result is from proc report followed by proc tabulate.

ods listing close ;
ods pdf file = "c:\temp\xtab.pdf" startpage = no style = sasweb ;
 
  proc report data = input nowd ;
    column date year, value ('Daily Total' value = value2) ;
 
    define date   / group  '' ;
    define year   / across  '' ;
    define value  / analysis sum '' format = dollar10.2 ;
    define value2 / analysis sum '' f = dollar10.2 ;
 
    rbreak after / dol skip summarize ;
    compute after ;
      date = 'Total' ;
    endcomp ;
  run ;
 
  proc tabulate data = input ;
    class date year ;
    var   value ;
    table    
          date = '' all = 'Total'
      , ( year = '' all = 'Daily Total' ) * value = '' 
            * sum = '' * f = dollar10.2 ;
  run ;
 
ods pdf close ;
ods listing ;

ODS TEXT = symbol

This blog post covers how to add a symbol from a font, in this case the up arrow from the Wingdings font.  The symbol I wanted to use was not a unicode character that I could find so I contacted SAS technical support.

If you use the Windows Character Map ( Start | All Programs | Accessories | System Tools | Character Map ), you will see the value as 0xE9.  This can be converted to a numeric value using a hex input format as follows:

value = input( 'E9', hex2. ) ;

An alternative way is to use Microsoft Word, click on the Insert toolbar then Symbols ribbon to open the Symbol dialog box (see below).

Now to render the 233 Wingdings symbol in ODS TEXT= do the following:

ODS TEXT = "^{style[font_face=Wingdings]%sysfunc(byte(233))}" ;

1 Color in 16.7 million

SAS / Graph and other applications use hexidecimal (base 16 or hex) values for colors. A single hex digit (1/2 an octet) can contain 16 values using 0 thru 9 then A (10) thru F (15). A byte can handle 256 colors (0 to 255 decimal or 00 to FF hex). A red, green, blue (RGB) primary color triplet (24 bit) can handle 16,777,216 colors as can be seen here:

data _null_ ;
  color1 = 16 ** 2 ;
  color3 = color1 ** 3 ;
  put ( color: ) ( @5  = comma10. / ) ;
run ;

    color1=256
    color3=16,777,216

If you need to obtain a specific computer color, you can do that using Microsoft Paint by following these steps:

1. Press Alt-PrintScreen to capture the screen
2. Start MS Paint and paste (control-V) the image
3. Click on the Color picker (eye dropper symbol)

4. Click on the desired color
5. Click on Edit Colors to see the decimal values

In this exmaple the returned decimal RGB values are 106, 89, 59. The following SAS macro function will convert the decimal RGB values to a SAS color hex (CX) value.

%macro rgbtohex( r, g, b ) ;
 CX%sysfunc(putn(&r.,hex2.))%sysfunc(putn(&g.,hex2.))%sysfunc(putn(&b.,hex2.))
%mend ;

%put %rgbtohex( 106, 89, 59 ) ;

CX6A593B

Bowling for averages

I was recently asked to find the value and name of the column that is closest to the average for a series of columns on a row. Random data was created to emulate the first attempt or roll in the ten frames in bowling.

The below code uses the automatic variable _N_ as a do-loop incrementor and for storing data in array elements. Automatic variables are added to the program data vector (PDV) but not output to a data set. Notice the use of the OF array to calculate the average and the first lowest ordinal value of the difference array (adiff). This was the first time I had occasion to use the vname() function used to identify the name of the column holding the value closest to the average.

data bowling ;
  length name $8 ; *-- make name first column in PDV ;
  array frame[ 10 ] ;
  array adiff[ 10 ] _temporary_ ;

  do name = 'Billy', 'Joe', 'Bob', 'Bubba', 'Junior' ;
    do _n_ = 1 to 10 ;
      *-- create random scores from 0 to 10 ;
      frame[ _n_ ] = floor( ranuni( 1234 ) * 11  ) ;
    end ;    

    average = mean( of frame[ * ] ) ;
    do _n_ = 1 to dim( frame ) ;
      *-- calc absolute difference between scores and average ;
      adiff[ _n_ ] = abs( average - frame[ _n_ ] ) ;
    end ;

    do _n_ = 1 to dim( adiff ) ;
      *-- find the score closest to the average ;
      *-- ordinal() returns smallest difference value from array list ;
      if adiff[ _n_ ] = ordinal( 1, of adiff[ * ] ) then do ;
        frame_value = frame[ _n_ ] ;
        frame_name = vname( frame[ _n_ ] ) ; *-- get column name ;
        leave ; *-- found it so exit the loop ;
      end ;
    end ;   

    output ; 
  end ;
run ;

Remeber this dude/dudettes, SAS abides...

Make it %local or go loco

Just spent an hour debugging a SAS macro called inside of an outer macro loop and both where using the same macro variable. You should always define macro variables as local within a macro definition. Failure to do so can result in percieved erratic behavior when the outer macro variable value is inadvertantly changed by the inner macro.

Macro parameters are always local by default, but you need to use the %local statement to protect your code. Lesson learned

%macro mymacro( myparam ) ;
  %local myvar ;
%mend ;

Reference: http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/viewer.htm#a000206835.htm

Controlling SAS/AF Frame Columns

I needed a SAS/AF frame to edit a Base SAS data set. The SAS data set contained six columns and I only wanted to expose the first three to the users. I also noticed on my first attempt that user edits were automatically being converted to uppercase even though there were no settings assignend to the data set's format or informat.

The solution to display the columns was to use an SCL list and assign the list to the SAS data set model's columnorder property. To prevent edits going to uppercase, the columns object uppercase property needed to be set to 'No'.

init:
   mdldata.editmode = 'tableleveledit' ; 
return ;

process:
  dcl list collist ;
  collist = { 'start', 'end', 'label' } ;
  mdldata.columnorder = collist ;

  do i = 1 to 3 ;
    /* allow data to be entered in mixed case */
    /* http://support.sas.com/kb/3/925.html   */
    mdldata.columns(i).uppercase = 'No' ;
  end ;
  tblviewer._refresh() ;
return ;

Dynamic Macro call using the RESOLVE() function

The below code uses the first dimension of a _temporary_ (not written to PDV) array as a dynamic parameter in a macro call. I was stumped on how to do this without writing a macro since the macro language is all text based.

Russ from SAS technical support suggested I use the RESOLVE() function. In order to avoid dropping the incrementor, I used the automatic _N_ variable which was inspired by Paul Dorfman in this paper.

The source code used in the %dsnobs( dsn = ) can be found here.

data rowcounts ;
  length name $16
         rows   8 ;
  array aname[ 2, 2 ] $16 _temporary_ 
    ( 
        'sashelp.cars', 'Cars Rows'
      , 'sashelp.class', 'Class Rows' 
    ) ;
  do _n_ = 1 to dim( aname ) ;
    name = aname[ _n_, 2 ] ;
    rows = resolve( cats('%dsnobs( dsn =', aname[ _n_, 1 ], ')' ) ) ;
    output ;
  end ;
run ;

Drop Missing Columns

The below macro was created to read all rows of every column and identify columns that only contain missing values. This comes in very handy in removing clutter (missing values) and more easily focus on columns contianing values instead of scrolling across the screen.

The code runs amazingly fast, about 1 million rows with 30 columns per second on my notebook.

%macro dropmissingcolumns( dsn ) ;
  %local lib
         mem 
         rowcount
         columncount
         jobstarttime ;

  %if %sysfunc( exist( &dsn. ) ) = 0 %then %do ;
    %if %sysfunc( exist( &dsn.,view ) ) = 0 %then %do ;
      %put ERROR: The data set &dsn does not exist. ;
      %return ;
    %end ;
  %end ;

  %let rowcount = %dsnobs( dsn = &dsn ) ;
  %if &rowcount = 0 %then %do ;
    %put NOTE: The data set &dsn contains zero rows of data.  Nothing to do. ;
    %return ;
  %end ;
  %let jobstarttime = %sysfunc( datetime() ) ;

  %if %index( &dsn., . ) > 0 %then %do ;
    %let lib = %upcase( %scan( &dsn., 1 ) ) ;
    %let mem = %upcase( %scan( &dsn., 2 ) ) ;
  %end ;
  %else %do ;
    %let lib = WORK ;
    %let mem = %upcase( &dsn. ) ;
  %end ;

  proc sql noprint ;
    select    name
            , count( name )
      into    :charlist separated by ' ' 
            , :charcount
      from    dictionary.columns
      where       libname = "&lib"
              and memname = "&mem" 
              and type    = 'char' ;

    select    name
            , count( name )
      into    :numlist separated by ' '
            , :numcount
      from    dictionary.columns
      where       libname = "&lib" 
              and memname = "&mem"
              and type    = 'num' ;
  quit ;

  %let columncount = %eval( &charcount. + &numcount. ) ;
  data _null_  ;
    length droplist $8192 ;
    set &dsn. end = eof ;

    %if &charcount. > 0 %then %do ;
      array charvars( * ) $ &charlist. ;
      array c_allmiss ( &charcount. ) $1 ( &charcount. * 'Y' ) ;
    %end ;

    %if &numcount. > 0 %then %do ;
      array numvars( * ) &numlist. ;
      array n_allmiss ( &numcount. )  $1 ( &numcount.  * 'Y' ) ;
    %end ;

    do i = 1 to &charcount. ;
      if not missing( charvars( i ) ) then c_allmiss( i ) = 'N' ;
    end ;
    do i = 1 to &numcount. ;
      if not missing( numvars( i ) )  then n_allmiss( i ) = 'N' ;
    end ;

    if eof then do ;
      dropcount = 0 ;
      droplist = '' ;
      do i = 1 to &charcount. ;
        if c_allmiss( i ) = 'Y' then do ;
          dropcount + 1 ;
          droplist = catx( " ", droplist, vname( charvars( i ) ) ) ;
        end ;
      end ;
      do i = 1 to &numcount. ;
        if n_allmiss( i ) = 'Y' then do ;
          dropcount + 1 ;
          droplist = catx( " ", droplist, vname( numvars( i ) ) ) ;
        end ;
      end ;
    end ;

    call symputx( 'droplist', droplist, 'l' ) ;
    call symputx( 'dropcount', dropcount, 'l' ) ;
  run ;

  %if &dropcount. > 0 %then %do ;
    data &dsn ;
      set &dsn( drop = &droplist ) ;
    run ;
  %end ;

  %put NOTE: dropped &dropcount missing of &columncount columns from &rowcount rows in %sysfunc( strip( %sysfunc( putn( %sysevalf( %sysfunc( datetime()) - &jobstarttime.),9.3) ) ) ) seconds. ;
%mend ;

Here is the source code on the %dsnobs(dsn=) macro used in the above code:

%macro dsnobs( dsn= ) ;
  %local nobs dsid rc  ;
  %let nobs = . ;
  %let dsid = %sysfunc( open( &dsn, i ) ) ; 
 
  %if &dsid %then %do ;
    %let nobs = %sysfunc( attrn( &dsid, nobs ) ) ; 
    %let rc = %sysfunc(close( &dsid ) ) ;
  %end ;

  &nobs 
%mend ;

Directory Listing without FILENAME PIPE

In the past when I needed a list of files in a directory I would use the filename command with the pipe device type as follows:

  filename dirlist pipe 'dir "c:\temp\" 

However, under Windows 7, I am getting the following error message noted here:

Stderr output:
There is not enough space on the disk.
NOTE: 0 records were read from the infile DIR.

Also, when code is sent to an IOM server that uses the FILENAME PIPE command, the code fails with ERROR: Access is denied or ERROR: Insufficient authorization. As a result, I wrote the below macro that utilizes SAS component language (SCL) functions to get around these restrictions.

%macro directorylisting
  (
      path   =
    , outdsn = dirlist
    , where  =
    , after  = 01Jan1960
  ) ;
 
  data &outdsn. ( keep = filename fullfilename created modified bytes ) ;
    attrib 
      dref         length = $8
      fref         length = $8
      folder       length = $256
      filename     length = $128 label = 'Filename'
      fullfilename length = $256 label = 'Full Filename'
      created      length = 8    label = 'Created'   format = datetime19.
      modified     length = 8    label = 'Modified'  format = datetime19.
      bytes        length = 8    label = 'Bytes'     format = comma15. ;
 
    if fileexist( "&path." ) then do ;
      folder = ifc( substr( "&path.", lengthn( "&path." ), 1 ) = "\"
        , "&path.", cats( "&path.", "\" ) ) ;
      rc     = filename( dref, folder );
      did    = dopen( dref ) ;
      dcount = dnum( did ) ;
 
      do i = 1 to dcount ;
        filename = dread( did, i ) ;
       
        if find( filename, "&where.", 'i' ) > 0 then do ;
          fullfilename = cats( folder, filename ) ;
          rc  = filename( fref, fullfilename ) ;
          fid = fopen( fref ) ;
          modified = input( finfo( fid, 'Last Modified' ), anydtdtm. ) ;
          if datepart( modified ) >= "&after."d then do ;
            created = input( finfo( fid, 'Create Time' ), anydtdtm. ) ;
            bytes   = input( finfo( fid, 'File Size (bytes)' ), 18. ) ;
            output ;
          end ;
          fid = fclose( fid ) ;
          rc  = filename( fref, '' ) ;
        end ;
      end ;
 
      did = dclose( did ) ;
      rc  = filename( dref, '' ) ;
    end ;
    else put "ERROR: The folder &path. does not exist." ;
  run ;
%mend ;

GetAttribs( dsn )

If you ever need to add a column in the middle of a SAS data set it can be problematic since the program data vector (PDV) controls the order. As a result, I wrote the below macro to expedite the process by writing column attributes to the SAS log. From there I can review then copy and paste the content into a new program.

%macro getattribs( dsn )  ;
  %if %sysfunc( exist( &dsn ) ) %then %do ;
    %let dsn = %upcase( &dsn ) ;
    %if %index( &dsn, . ) %then %do ;
      %let libname = %scan( &dsn, 1, . ) ;
      %let memname = %scan( &dsn, 2, . ) ;
    %end ;
    %else %do ;
      %let libname = WORK ;
      %let memname = &dsn ;
    %end ;
    
    data _null_ ;
      length line $512 ;
      put /  "Attributes of data set: &dsn"
         //  'attrib' ;
      do until( eof ) ;
        set sashelp.vcolumn end = eof ;
        where libname = "&libname" and memname = "&memname"  ;
        line = cat( strip( name )
          , ' length = ', ifc( type = 'char', '$', '' ), strip( put( length, 5. ) )
          , ifc( missing( label ), ' ', cat( ' label = "', strip( label ), '" '  ) )
          , ifc( missing( informat ), ' ', catx( ' ', ' informat =', informat ) ), ' '
          , ifc( missing( format ), ' ', catx( ' ', ' format =', format ) )
        ) ;
        put +2 line ;
     end ;
     put ';'  ;
     stop ;
   run ;
 %end ;
 %else %put NOTE: Data set &dsn does not exist. ;
%mend ;

%getattribs( sashelp.cars )

Attributes of data set: SASHELP.CARS

attrib
  Make length = $13
  Model length = $40
  Type length = $8
  Origin length = $6
  DriveTrain length = $5
  MSRP length =  8   format = DOLLAR8.
  Invoice length =  8   format = DOLLAR8.
  EngineSize length =  8 label = "Engine Size (L)"
  Cylinders length =  8
  Horsepower length =  8
  MPG_City length =  8 label = "MPG (City)"
  MPG_Highway length =  8 label = "MPG (Highway)"
  Weight length =  8 label = "Weight (LBS)"
  Wheelbase length =  8 label = "Wheelbase (IN)"
  Length length =  8 label = "Length (IN)"
;
NOTE: There were 15 observations read from the data set
      SASHELP.VCOLUMN.
      WHERE (libname='SASHELP') and (memname='CARS');

Timer Macro

The TIMER macro writes the elapsed time between calling this macro with MODE=ON and MODE=OFF. This comes in very handy when you want to find the slow parts of your code.

%macro timer
  (   mode = ON
    , description = Duration
    , reset = Y
  ) ;
  %global timermacro_starttime ;

  %if %upcase( &mode ) = OFF %then %do ;
    %if %symexist( timermacro_starttime ) %then do ;
      %put &description %sysfunc( putn( %sysevalf( %sysfunc( datetime() )
        - &timermacro_starttime ), 12.3 ) ) seconds @ %sysfunc( time(), time8. ) ;
      %if %upcase( &reset ) = Y %then %symdel timermacro_starttime ;
  %end ;
  %else %let timermacro_starttime = %sysfunc( datetime() ) ;
%mend ;
Example:

  %timer()

    data _null_ ;
    run ;

  %timer( mode = off ) 

    Duration 0.153 seconds

Enhanced Editor Preferences

Here are some suggested Enhanced Editor settings and related best practices. Select Tools | Options | Enhanced Editor... and enabled Show line numbers, Insert spaces for tabs and Replace spaces with tabs on file open. I also like to set my tab size to 2 spaces (see below).

The other suggested setting is achieved by selecting Tools | Options | Preferences... and setting Recently used file list to the maximum value of 30 entries.

With those settings in place here are some suggested best practices when writing code:

• One line of code (ends in semicolon) per line making it easier to read and maintain
• Indent each subservient line with 2 spaces
• Start each SQL column with a comma so it is self contained and easy to comment out
• Align columns as appropriate to make it easier to read and maintian

Example

    proc sql noprint stimer ;
      connect to odbc ( "%getconnection( server = Prod )" ) ;
        create table commodity as
          select   *
            from   connection to odbc
            (
              select        commoditycode
                      /*  , commodityabbreviation
                          , commodityname   */
                          , count(*) as count
                from        arm.common.commodity       
                where       commoditycode = '0041'
                group by    commoditycode
                      /*  , commodityabbreviation
                          , commodityname   */
                order by    commoditycode
                      /*  , commodityabbreviation
                          , commodityname   */
            ) ;
      disconnect from odbc ;
    quit ;

Compare and contrast the ease of using leading commas versus a conditional trailing comma in this macro based example:

    %do i=1 %to 10;
      , yr&i._acre_qty as acre&i 
      , yr&i._yield_qty as yield&i
    %end;

or

    %do i=1 %to 10;
      %if &i<10 %then %do;
        yr&i._acre_qty as acre&i ,
        yr&i._yield_qty as yield&i ,
      %end;
      %else %do;
        yr&i._acre_qty as acre&i ,
        yr&i._yield_qty as yield&i 
      %end;
    %end;

Enhanced Editor Shortcut Keys

The SAS Enhanced Editor contains many shortcut keys that can improve productivity. See the below table for a list of some of the more powerful, yet often unknown commands and their related keys.

Shortcut	Keys
Bookmark (toggle) line	Ctrl + F2
Comment selection with comments	Ctrl + /
Context Menu	Shift + F10
Convert selected to lowercase	Ctrl + Shift + L
Converted selected to uppercase	Ctrl + Shift + U
Go to next marked line	F2
Go to previous marked line	Shift + F2
Indent selected text	TAB
Move to matching brace/paren	Ctrl + [
Move to matching DO/END keyword	Alt + [
Rectangular selection	Alt + left mouse button (LMB)
Remove trailing blanks (white spaces)	Ctrl + Shift + W
Select all	Ctrl + A
Uncomment selection with line comments	Ctrl + Shift + /
Unindent selection	Shift + Tab

Examples

Bookmarking - select the desired line and use the Ctrl + F2 keys to bookmark the line (a cyan image will appear to left of the code). Now if you scroll anywhere else in the file you can quickly return to this bookmark by using the F2 key while Shift + F2 takes you to the previous mark if you have multiple bookmarks. Press Ctrl + F2 again to remove a bookmark.

Rectangular Selection - Use the combination of the Alt + LMB to define a rectangular selection. Notice in the below example that we are able to select the table prefix and easily remove that reference using this technique.

Reference: Doing More with the SAS Display Manager: From Editor to View Table - Options and Tools You Should Know by Art Carpenter.

Macro Quoted String > 262 characters

If you have a SAS macro variable that is longer than 262 characters in length you will get a WARNING message such as the one shown below:

WARNING 32-169: The quoted string currently being processed has become more than 262 characters long. You may have unbalanced quotation marks.

The way to get around this limitation is to use the option named NOQUOTELENMAX as in

OPTIONS NOQUOTELENMAX ;

SAS Explorer Detailed View

The default display of the SAS Explorer is to show large icons as is shown below.

My preference is to display the same content as smaller images with more information such as is shown below.  In order for this to happen, consider adding the following display manager (dm) command to your autoexec.sas file:

dm "explorer 1; details on" ; *-- set explorer to detailed view;

SAS Catalogs

SAS catalogs are heavily underutilized in my experience. Catalogs are analogous to a Zip file and are used to store formats, macros, SCL, graphics and more in catalog entries based on its type. Because catalogs are part of the SAS/BASE product, it is also a great way to store dynamically generated code then call it later on. Another benefit of using SAS catalogs to store information is that it is portable and does not have dependencies on OS storage devices that can change (e.g. does everyone have a c:\temp\ folder?).

A catalog is fully identified by a four-level name such as libref.catalog.entry-name.entry-type:

libref - the library that will store the catalog (use WORK for throw away entries)
catalog - name of the catalog (think of it as a zip file)
entry-name - name of the catalog entry
entry-type - types include SOURCE, LOG, OUTPUT, FORMAT, MACRO, CATAMS, SCL, GRSEG

The below code uses the disposable WORK libref to dynamically write out and then execute SOURCE code using both the full four-level name and the abbreviated version.

filename fullname catalog "work.catalog.mycode1.source" ;
filename abbrev   catalog "work.catalog" ;

data _null_   ;
  file fullname ;
  put "data _null_ ;"
    / "  put 'Hello from fullname' ;"
    / "run ;" ;
  file abbrev(mycode2.source) ;
  put "data _null_ ;"
    / "  put 'Hello from abbrev' ;"
    / "run ;" ;
run ;

%inc fullname ;
%inc abbrev( mycode2.source ) ;

filename fullname clear ;
filename abbrev clear ;

Reference: Using a SAS Catalog to Develop and Manage a SAS Project, by David D Chapman

Adding custom tips to GTL output

The default behavior for mouse over events is to show only the values being plotted. I contacted SAS tech support to find out how to display other variables in the output. The key is to use the rolename=(tip1=column) tip=(tip1) syntax shown below.

proc template ;
  define statgraph sgplot ;
    begingraph ;
      layout overlay ;
        ModelBand "G63LAMQ2" /  
          display=(outline) OutLineAttrs=GraphPredictionLimits 
          Name="MODELBAND" LegendLabel="95% Prediction Limits" ;
        ModelBand "G63LAMQ3" /  
          Name="MODELBAND1" LegendLabel="95% Confidence Limits" ;
        ScatterPlot X=Age Y=Weight / 
          primary=true 
          rolename=(tip1=name tip2=sex tip3=age tip4=weight) 
          tip=(tip1 tip2 tip3 tip4) ;
        RegressionPlot X=Age Y=Weight / 
          NAME="REG" LegendLabel="Regression" clm="G63LAMQ3" cli="G63LAMQ2" ;
        DiscreteLegend "MODELBAND" "MODELBAND1" "REG" ;
      endlayout ;
    endgraph ;
  end ;
run ;

ods graphics / imagemap = on ;
ods listing close ;
ods html path='c:\temp' file='tips.html' ;

  proc sgrender data=sashelp.class template=sgplot ;
  run ;

ods html close ;
ods listing ;

getconnection() - macro function

I wanted a way to call a SAS macro as a function to return a streaming value that would be rendered in place. Doing so would by definition eliminate global macro variable collisions.

The below source code does just that using all %local macro variables and only macro code. The returned value (a connection string to a remote database server) is used in place which will eliminate hard coding issues when server definitions changes as they always do over time.

The next image reveals the trusted connection strings used as DSN-less connections followed by the SAS macro function.

proc sql noprint stimer ;
   connect to odbc ( "%getconnection(server=prod)" ) ;
     create table mydsn as
          select       *
            from       connection to odbc 
            (
              select     *
                from     db.schema.table 
            ) ;
   disconnect from odbc ;
quit;


%macro getconnection( server = Prod ) ;
  %local libref
         path 
         closelib 
         rc
         dsid 
         returncolumn
         connection ;

  %let libref       = afutil ;
  %let path         = c:\xport\ ;
  %let closelib     = N ;
  %let returncolumn = connection ;

  %if %sysfunc( libref( &libref. ) ) ne 0 %then %do ;
    %let rc = %sysfunc( libname( &libref., &path. ) ) ;
    %if &rc ne 0 %then %do ;
      %put ERROR: could not create the libname &libref.. ;
      %return ;
    %end ;
    %let closelib = Y ;
  %end ; 

  %let dsid = %sysfunc( open( &libref..servers( where = ( upcase( server ) = upcase( "&server." ) ) ) ) ) ;                                                                          
  %let rc = %sysfunc( fetch( &dsid. ) ) ;
  %if &rc ne 0 %then %do ;
    %put ERROR: no entry found in &libref..servers where upcase( server ) = upcase( "&server." ) ;
    %let rc = %sysfunc( close( &dsid. ) ) ; 
    %return ;
  %end ; 
 
  %let connection = %sysfunc( getvarc( &dsid., %sysfunc( varnum( &dsid., &returncolumn. ) ) ) ) ;                                                                
  %let rc = %sysfunc( close( &dsid. ) ) ; 

  %if &closelib. = Y %then %let rc = %sysfunc( libname( &libref. ) ) ; 

  &connection.    
%mend ;

Moving Average

The below code reveals the concept of calculating a moving average. A _temporary_ array retains its values and is not included in the program data vector (PDV). The use of modulus division is critical in replacing the most recent value with the oldest retained value in the array.

%let rows = 3 ;
data movingaverage ;
  array ave[ &rows ] _temporary_ ; 
  do i = 1 to 5 ;
    subscript = mod( i , &rows ) ; 
    subscript = ifn( subscript, subscript, &rows ) ;
    ave[ subscript ] = i ; 
    if i >= &rows then average = mean( of ave[ * ] ) ;
    output ;
  end ;
run ;

It varies

If I extract source code from a SAS catalog entry of type source, all the lines are padded with spaces to the LRECL value which defaults to 128. Therefore, when you copy and paste the source code to print it, the lines come out as double spaced due to the line length.

This is where the $VARYING. format comes into play. In the below code, the input file is read in as $CHAR256. to retain the leading spaces then the line LENGTH() is determined after trailing spaces have been removed with the TRIM() function. That line length value (len) is added to the $VARYING. format which will now keep my leading spaces or indentation and remove the trailing spaces.

The CLIPBRD (clipboard) access method is used so that the contents are written to the computer's clipboard so that the trimmed contents can be pasted to whatever destination you desire.

filename xin catalog "lib.cat.entry.source" ;
filename xout clipbrd ;
data _null_ ;
  infile xin ;
  file xout ;
  input line $char256. ;
  len = length( trim( line ) ) ;
  put line $varying256. len ;
run ;
filename xin clear ;
filename xout clear ;

What remote libnames are assigned?

I was attempting to upload some SAS formats from my local PC to the SAS server and while there was no error message in the log, the formats did not get updated. I had assumed the APFMTLIB was already assigned but it was not and the way I determined that was via the following code.

signon ;
  rsubmit wait = yes ;

    proc sql ;
      create table onserver as
        select   libname
               , path
          from   dictionary.libnames ;
    quit ;

    proc download
      data = onserver
      out  = localdsn ;
    run ;

  endrsubmit ;
signoff ;