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)

SAS: FTP an external file (flat file)

I had a lot of trouble with SAS' FTP access engine. It appeared to be randomly corrupting the data.  This worked though. I relied heavily on the indicated web page, and then converted it to a macro.


* ----------------------------------------------------- ;
*              M A C R O :  F T P F I L E
* ----------------------------------------------------- ;
*  FTP the hard way...but to overcome failure of the
*  FTP access engine, which corrupted the data.
*  Source: https://communities.sas.com/message/114111
*  Bill Qualls, 20150326
* ----------------------------------------------------- ;

%macro ftpfile(folder=&RAWDATA_FOLDER, file=, ftpcom=&RAWDATA_FOLDER/ftpcom.txt);

* file to which ftp commands will be written ;
filename ftpcom "&FTPCOM" lrecl=80;

data _null_;
file ftpcom;

* ftp ession user and password ;
put "user theuser thepassword";

* disable prompting cmd ;
put "prom no";

* enable binary transfer mode cmd ;
put "bin";

* go to local folder cmd ;
put "lcd &FOLDER";

* go to remote folder cmd ;
put "cd OurFolderOnFTPServer";

* send file cmd ;
put "send &file";

run;


* set up piping into ftp commands into ftp instruction ;
filename doftp pipe "ftp -n ftp.xxxxxxxx.com < &FTPCOM";


* enable ftp session and pass it the commands file ;
data _null_;
infile doftp;
input;
put _infile_;
run;

%mend ftpfile;