SAS: Hash table lookup

SAS hash table lookup example taken from http://support.sas.com/documentation/cdl/en/lrcon/65287/HTML/default/viewer.htm#n1b4cbtmb049xtn1vh9x4waiioz4.htm and included in this bog so I can find it easily.


data match;
   length k 8;
   length s 8;
   if _N_ = 1 then do;
      /* load SMALL data set into the hash object */
     declare hash h(dataset: "work.small");
      /* define SMALL data set variable K as key and S as value */
      h.defineKey('k');
      h.defineData('s');
      h.defineDone();
      /* avoid uninitialized variable notes */
      call missing(k, s);
   end;

/* use the SET statement to iterate over the LARGE data set using */
/* keys in the LARGE data set to match keys in the hash object */
set large;
rc = h.find();
if (rc = 0) then output;
run;

SAS: SAS on the Mac

I have little experience with the Mac, and no experience with SAS on the Mac. Some of my students had trouble running SAS on the Mac. One of my students sent me this message. Saving it here just in case...

1. Exit the VM

2. Go to 'Terminal'

3. Type    sudo nvram boot-args=debug=0x10

4. Run the VM again and the bug will be fixed

R: Dataframe slice example

R dataframe slice example.

Note: slice = whole[rows satisfying this condition, tuple of column names]

    > head(mywhole)
      obs ew id ba x
    1   1  E  1  B 8
    2   2  E  1  A 9
    3   3  E  2  B 4
    4   4  E  2  A 3
    5   5  E  3  B 6
    6   6  E  3  A 7
    > myslice = mywhole[ba=="B", c("id","x")]
    > myslice
       id x
    1   1 8
    3   2 4
    5   3 6
    7   4 6
    9   5 5
    11  6 9
    13  7 6
    15  8 5
    17  9 7
    19 10 9

SAS: Using "in" within a macro %if

This was new to me…want to use an “in” within a macro “%if”?  Then you need to use the sas option minoperator.  I found a use for it as follows:

options minoperator; * required for in within a macro if ;   

%if (&RUN_TIME_ROUNDED in &GLBL_EMAIL_DASHBOARD_RECON_WHEN) %then %do;

Where

%let GLBL_EMAIL_DASHBOARD_RECON_WHEN = (21600 43200 64800);

Note “in” values are space-delimited. Comma-delimited requires yet another macro option (not system option).  Details at http://support.sas.com/kb/35/591.html

SAS: Create an empty file

There are two ways to create an empty SAS dataset having the same fields and formats as an existing data set. I knew about the DATA step way (shown first) but did not know about the PROC SQL way (shown second):


data work.orig;
a = 1; b = 2; c = 3; output;
a = 2; b = 3; c = 4; output;
format b 3.1 c 4.2; * to show format is copied also ;
run;

data work.copy1;
set work.orig;
delete;
stop;
run;

proc contents data=work.copy1;
run;

proc sql noprint;
create table work.copy2 like work.orig;
quit;
run;

proc contents data=work.copy2;
run;

SAS: PROC PWENCODE

Something that has always bothered me is that the passwords for our databases are readable to anyone who can see our SAS code.

I define all database user IDs and passwords as macro variables in a constants file which is %included in my programs.  They are used in the libname statement as follows:

libname XXXLIB odbc datasrc=oracle user=&GLBL_ORACLE_USERID password="&GLBL_ORACLE_PASSWORD" schema=XXX;

A coworker was nice enough to point me to PROC PWENCODE, which I was not previously aware of.

This proc encodes a password. The encoded password appears in the log, and is stored as macro variable _PWENCODE. 

I decided to try it with a UP file to see if it worked.  Here is my code:

* encode the real password... ;

proc pwencode in="&GLBL_ORACLE_PASSWORD";

run;

%put The encoded password is &_PWENCODE;

%let GLBL_ORACLE_PASSWORD = &_PWENCODE;

libname XXXLIB odbc datasrc=oracle user=&GLBL_ORACLE_USERID password="&GLBL_ORACLE_PASSWORD" schema=XXX;

The encoded password looks like this – the {SAS002} prefix is recognized by the SAS access engine and decoded at that time:

The encoded password is {SAS002}8083755C07C000D90BB19F7A20A761E9

So I could encode the password in a one-time program, then store that password (with the {SAS002} prefix) as GLBL_ORACLE_PASSWORD in my constants file.

Of course, it would still be plain text in the constants file, but at least it would be useable only thru SAS, and we would not be exposing the “real” password to the whole world.

Excel: Generate random numbers following a Normal distribution

Here's how to generate random numbers following a Normal distribution using Excel:

=norminv(rand(),mean,stdev)