SAS Tips (and some tricks)

• Check for valid email addresses
• SAS sounds
• Keeping passwords out of autoexec.sas
• Faster PROC SQL using Pass Through
• Setting up an ODBC connection to Callista
• Mapping course code + unit set to faculty and school
• Indian Restaurant Reviews
• SAS Output Options
• Exporting large data sets from SAS into Excel
• SAS ODBC
• Proc Summary alternatives
• Drawing a cowboy hat
• Creating a format from a dataset
• Viewing the contents of a format
• Converting from text to numeric
• Adding a row number in Proc SQL
• Importing long text fields from Excel
• Using R for charts
• What does an evening flopper look like?
• Getting current folder
• Reading from Excel
• Squirrel danger
• Adding student names to datasets
• Displaying format details
• SAS Functions list [Very Useful]

If that's not enough then we also have a whole page of SAS code samples.

Check for valid email addresses

Here's a sample of how to check that email addresses are valid. It's not 100% accurate, but is pretty close. It looks for one valid address per line, so multiple addresses separated by a comma or semi-colon will fail. If you need help modifying it for your particular needs then look up regular expressions on the interweb, e.g. SAS Support.

 
data _null_;
   retain re;
   length email $ 150;
 
   if _N_ = 1 then do;
     /* Simple regex for most common email addresses */
      regexp = "/^[\w.%+-]+@[\w.-]+\.[a-z]{2,4}\s*$/i";
      re = prxparse(regexp);
      if missing(re) then do;
         putlog "ERROR: Invalid regexp " regexp;
         stop;
      end;
   end;
 
   input email;
 
   if ^prxmatch(re, email) then
      putlog "invalid: " email;
  else
    putlog "     ok: " email;
datalines;
not.an.email
not@an.email.either
john.smith@uwa.edu.AU
frank@hotmail.com
frank@home.com,frank.work.com
; 

SAS sounds

You can cause SAS to emit tuneful beeps from the PC speaker by specifying frequency and duration of notes. The following is an attempt at Love Will Tear You Apart, by Joy Division.

 
data _null_;
 
call sound(146,32); *e1;      
call sound(164,256); *e1;      
call sound(185,64); *fis4; 
call sound(196,32); *g8; 
call sound(185,64); *fis4;
call sound(164,32); *e8;
call sound(146,64); *d4;
call sound(123,128); *b2;
call sound(146,32); *d8;
call sound(110,128); *a2;
call sound(146,32); *d8;
call sound(164,256); *e1;
call sound(185,64); *fis4;
call sound(196,32); *g8;
call sound(185,64); *fis4;
call sound(164,32); *e8;
call sound(146,64); *d4;
call sound(123,128); *b2;
call sound(146,32); *d8;
call sound(110,256); *a1;
 
run;

Old McDonals

Keeping passwords out of autoexec.sas

It's convenient to have complete connection details to SIMS within the SAS autoexec.sas file, such as:

libname sims odbc
   datasrc  = 'SIMS'    /* the DSN set up previously */ 
   user     = 'q_myname'
   password = 'myPassword'
   schema   = SIS_OWNER;

However, if you don't want to have your password stored in a text file then you can get SAS to prompt for the password whenever the SIMS connection is required.

libname sims odbc
   datasrc  = 'SIMS'    /* the DSN set up previously */ 
   user     = 'q_myname'
   dbprompt = YES
   defer    = YES
   schema   = SIS_OWNER;

Faster PROC SQL using Pass Through

Whenever possible SAS will convert PROC SQL statements into native SQL that can be processed on the database. For example, in the following only the matching records are passed back from SIMS to SAS.

proc sql;
    select * 
    from sims.course_type 
    where govt_course_type = 3;
quit;

However, more complicated SQL will cause SAS to retrieve the entire contents of the relevant tables, and do the processing itself. This can be very slow. To avoid this you can ask for SAS to send the whole SQL statement intact to the database for processing, a facility known as SQL pass through. This is done by specifying the connection in the following manner (note that you can't use a LIBNAME):

proc sql;
    connect to odbc(dsn=sims uid=q_myname password=mypassword);
    create table test1 as
    select * from connection to odbc
    (
        select * from course_cat 
    );
quit;

If you don't want your password sitting in the code then you can make it prompt for the password (and/or username) like so:

proc sql;
    connect to odbc(dsn=sims uid=q_jminchin dbprompt=YES);
    create table test1 as
    select * from connection to odbc
    (
        select * from course_cat 
    );
quit;

Using SQL pass through can reduce the time a SAS step takes from tens of minutes to mere seconds. However, you should only use it when you've tried the standard SAS way and found it too slow.

Setting up an ODBC connection to Callista

To run ODBC query tools, such as Excel, Access and SAS, against a Callista database, you must first set up an ODBC data source name (DSN) pointing to the database.

• Go to Administrative Tools / Data Sources (ODBC) which is normally found under Start / Settings / Control Panel and sometimes under Start / Programs.
• Choose the System DSN tab.
• Click the Add button.
• Choose Microsoft ODBC for Oracle as the driver and then click Finish.
• Enter the Oracle database name (eg SIMSPROD) in the Data Source Name field and, for consistency, in the Description field, and enter the appropriate location (e.g. simsprod.acs.uwa.edu.au). Optionally, enter your username and click OK.
• In the query tool, connect using the name of the DSN you have created.

When you access Callista through a non-Callista program you will only have privileges from roles which are ticked in Callista as your default roles. To minimise accidental update or deletion of data it is best to have a read-only default role and make any updating roles non-default.

Setting up the Oracle client

You may be able to skip this step.

If the Oracle client hasn’t been installed on your pc, then you could ask ITS to install it, or if feeling adventurous, do it yourself -

1. Double-click on \\admin-serv47\public\sims\pcseup\oracle.reg - if you don’t have access to this folder, use the local copy at p:\software\oracle.
2. Modify the path environment variable to add k:\ora8wnt\bin to the start of the path (where k is connected to "\\serv08\public"). You do this in the system control panel. Choose advanced, click the environment variables button then at the bottom select the path variable and edit it. Add k:\ora8wnt\bin; at the start eg k:\ora8wnt\bin;C:\Program Files\CA\Dcs\DMScripting\;C:\Program Files\CA\DCS\CAWIN\;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\tngsd\BIN;C:\CA_APPSW;

Add SIMS as a SAS library

To create a SAS library accessing using this connection, add the following code to your autoexec.sas file:

/** set up libname which points to SIMS via ODBC **/

libname sims odbc
datasrc  = 'SIMSPROD'    /* the DSN set up previously */
user     = 'q_lminchin'
password = 'whatever'    /* enter your password here */
schema   = SIS_OWNER;

Mapping course code + unit set to faculty and school

Indian Restaurant Reviews

Fluffy N. writes

At Anghiti in Innaloo, I recommend:
• Dhal Makhani – Black lentils cooked slowly on tandoor overnight and tempered with garlic and green chillies.
• Reshmi Paneer – Grated paneer with onion and tomato masala (this one’s my fav – haven’t seen it elsewhere)
• Aloo Mutter – Peas and potatoes with sliced ginger and lots of cumin seeds
• Aloo Baingan – The aubergine dish is prepared with exotic herbs and spices
• They’ve got some other general favs like Palak Paneer and Malai Kofta but haven’t actually tried them.

Fabulous service – really lovely wait staff. And there’s always Indian people eating there so it’s got to be good :)

And here are the ratings:

• Toilets – 5 / 10 - Old, but clean.
• Car parking – 5 / 10 - Small and busy. But have always found a parking spot.
• Spoons – 9 / 10 - Very good. Seem to work fine.
• Menu humour – 9 / 10 - None. But they have an excellent take-away colour booklet menu.
• Ambience – 6.75 / 10 - Not over the top or anything special but it’s reasonable.

Well the family once again dined at the Cinnamon Club last Sat. night (1 Nov).

Ordered Samosas for entree. Quite small and not very hot.

For main dishes we ordered:

• Chicken Tikka Masala
• Rogan Josh, and
• Dhal Makhani

with Saffron Basmati Pulao.

And, of course, no Indian meal would be complete without those delicious Indian breads:

• Aloo Paratha, and
• Kheema Naan

The mains were very tasty and certainly recommended.

The Kheema Naan wasn't enjoyed as much as the Aloo Paratha.

We also ordered and shared the Mango Kulfi for dessert.

Together with two rather expensive glasses of Evans and Tate Margaret River Classic, a glass of Coke and a Lemon Lime & Bitters, the total damage was approx. $120.

It's not fantastic value for money by any stretch but the venue is modern and comfortable and the food generally good.

And so ... to Rob's rating (scored out of 10):

• Food - 6.5
• Service - 7
• Atmosphere - 8
• Value - 6

Food review for the Indian restaurant "Bollywood" in Innaloo. (Jan 2009)

Entrée: We had Samosas and panneer kebab, Lamb tandoor - 10/10

Mains:

• Prawn Curry masala
• Madras Lamb curry
• Butter Chicken
• Channa masala
• Mutter Panneer

Served with Basmati Rice - 5/10

Dessert Kulfi - 7/10

Very spacious restaurant.

The atmosphere was great - 10/10

Food was not that great - 7/10

I'll go again.

RobR writes:

On Sat. 10 Jan 2008 the family ventured out to Padbury to dine at the Koh-I-Noor Indian Restaurant which had been recommended to me by Latha.

On arrival at approx. 6:30pm I bumped into Harvey von Bergheim who was popping in for some take-away. He mentioned that he was a regular at the restaurant so already I thought we were on to something good!

The interior of the Koh-I-Noor leaves much to be desired. Very spartan decorations, feels more like an office canteen than a restaurant.

But we weren’t there for a romantic night out. We just wanted good Indian food!

We were warmly welcomed by the staff and shown to our table.

(The restaurant is byo with $5 corkage. Cokes are $3 per glass which is quite expensive for Padbury)

The restaurant was about ¼ full so there wasn’t much queuing necessary at the rather small bain maree. After confirming the buffet arrangements it was time to tuck in!

First the entrees:

soup

3 flavours of naan (plain, garlic, meat of some sort)

small pappadums

a few Indian salads

some sort of battered calamari

tandoori chicken pieces

other dishes I’ve forgotten …

The soup was ok, the naan was good.

Everybody enjoyed the chicken and calamari and we kept going back for more of these dishes.

No complaints so far …

Onto the main dishes:

I’m pretty sure there were 3-4 curries including beef vindaloo, lamb dhansak and fish hyderabadi.
dhal
a chick-pea dish.

This is where the meal started to go wrong …

All the meat curries tasted pretty much the same with little to no spice. They  were also gravy-like in texture. In other words they’d been prepared with the steak-and-veg punter in mind.

The fish had a strange flavour, not to my liking. The dhal was very ordinary and the chick-pea dish inedible (by me).

We all just went back for more entrees! Which is one of the good things about a buffet …

The sweets included, oddly enough, a pavlova(!), some sort of custard-like mousse dish (sweet and tasty), lots of Peters’ ice-cream for the kiddies and a couple of Indian sweets which nobody was eating.

Coffee was self-serve from one of those horrible automatic machines

So … after a good start (entrees) I’m afraid it was all down hill for me.

If you’re an aficionado of Indian cuisine, then stay away from the Koh-I-Noor!

At $33.50 per head it’s not particularly good value and I’d rather spend the money ordering a la carte from a decent Indian restaurant closer to the CBD (The Royal India comes to mind)

And so ... to Rob's rating (scored out of 10):

• Food – 4 (Entrée:6 Mains: 3)
• Service – 7 (waiters are friendly and quick at removing plates from the table)
• Atmosphere - 1
• Value – 3.5

SAS Output Options

SAS can produces graphs from datasets using code. Look at Graph-N-Go, such as in this introduction.

The resultant graph can be exported to a gif file or an interactive Java web page. This latter type lets a user without SAS manipulate the graph, such as converting a vertical bar chart into a horizontal one, or choosing different data to group on.

Another export option is to source code, which shows the SAS code required to produce the graph. From this is can be seen that PROC GCHART is being used.

PROC PLOT produces crude text plots of data.
PROC GPLOT produces more refined graphical plots of data.
PROC REPORT is like PROC PRINT, but with a few adjustments making the output more presentable for reporting.

Exporting large data sets from SAS into Excel

The SAS ODBC driver (see following section for installation and usage instructions) makes it possible to create pivot tables and data cubes directly from existing SAS data libraries (avoiding intermediate exporting of data into Excel worksheets). Data cubes are especially useful for data sets that are too large to process in Excel.

SAS ODBC

The most direct way of importing data from SAS into Excel is by using the (free) SAS ODBC driver (available on the SAS Client Install disc). Installation of the driver is a little tricky, so follow the instructions below carefully.

Installation

1. Install the SAS ODBC driver (if not already installed) from the SAS Client Install disc.
2. Set up an ODBC connection to your local SAS. Choose User or System DSN based on the number of prospective users:
   
3. In the SAS ODBC Driver Configuration first go to the Servers tab, and in Server Settings add the Name of your PC, then click Configure and accept the defaults. Then click Add to move that to the list of Servers.
   
4. In the General tab give your data source a name, such as MySAS, and ensure Server has your PC selected. Then click OK.
   
5. Now you can see your new data source in the list.
   
6. The final step is to add SAS to your local services file. This is usually \Windows\system32\drivers\etc\services. Open this file in notepad, and add a line for your SAS connection. The first item is the name of your PC, eg. ADM788, and then is a free port number above 1024. In the example below I’ve used 5010.
   

How it works

When queried, the driver starts up SAS on the local machine, and retrieves data through SQL commands.

How to use it

1. Start Excel
2. Either select Data | PivotTable and PivotChart Report or select Data | Import External Data | New Database Query.
3. Choose to get the data from an external data source.
4. In Choose Data Source, in the database tab, select your SAS ODBC connection, eg. MySAS.
5. Select the columns from SAS to use. Initially all datasets from all libraries will be shown, but you can choose to only show datasets from a specific library in Options.
6. If you want, you can manually adjust the SQL statements in Microsoft Query, perhaps to filter the retrieved data.

Proc Summary Alternatives

Method 1 - Proc Sql

proc sql;

  create table multipleUnits1 as 
  select student, count(unit) "Units"
  from deet08.ld2008f
  where course = '10110'
  group by student
  ;

quit;

Method 2 - First and Last

proc sort data=deet08.ld2008f(where =(course = '10110') )
  out = sortedUnits;
  by student;
run;

data multipleUnits2(keep = student units);
  set sortedUnits;
  by student;

  if first.student then Units = 0;	 
    Units + 1;
    if last.student and Units > 1 then output;
run;

Method 3 - Proc Summary

proc summary data=deet08.ld2008f nway;  
	where course = '10110';
  class student;
  output out=multipleUnits3(drop = _TYPE_ rename=(_FREQ_=count));
run;  

Drawing a Cowboy Hat

A commonly encountered question is "How do I draw a cowboy hat in SAS?" So by popular demand, here is the answer:

goptions cback=black colors=(white cyan magenta gold yellow);

data hat;
   do x=-5 to 5 by .25;
      do y=-5 to 5 by .25;
         z=sin(sqrt(x*x + y*y));
         output;
      end;
   end;
run;

title1 c=white f=swiss 'The Cowboy Hat';
title2 h=2 angle=90 ' ';

proc g3d data=hat;
   plot y*x=z / ctop=yellow ctext=white; 
run;

Creating a format from a dataset

Given a dataset, how is it possible to create a format from it? For example, to create a format of course names from the Callista course table.

/* must have AT LEAST the three key vars: FMTNAME START LABEL, as input to CNTLIN 
   HLO keyword (optional) is used as follows:
        'H'=high, 'L'=low and 'O'=other (allows selecting values for HIGH, LOW or OTHER)
   END var is also optional for end of range if required */

data course(keep=fmtname start label hlo);
    
  set simsprod.course_version end = lastrec;
  retain fmtname "$course";
  start=compress(course_cd||put(version_number, z1.));
  output;

  if lastrec then do; /* when you hit last record you want to output another record for the OTHER category */
    hlo = 'O'; 
    title = 'no name found for this course';
    output;
  end;
  
  rename title=label;
  
run;

proc format cntlin=course;
run;

For those who think in SQL, here's an alternative version:

proc sql;
	create table course as
	select title as label,
		'$course' as fmtname, 
		compress(course_cd || put(version_number, z1.)) as start,
		'' as hlo
	from simsprod.course_version
	union
		select 'no name found for this course' as title,
		'$course' as fmtname,
		'' as start,
		'O' as hlo
	from simsprod.course_version
	;
quit;

Viewing the contents of a format

Ever wondered what's stored in your format catalogs?

libname library 'P:\stats\SASPROD\Formats'; libname x 'c:\saswork';

proc format library=library.formats
cntlout = x.cntlout;
run;

Converting between numeric and character

Use the input command to convert from a character string to numeric. If you have a left- justified string prefixed by zeroes then the second parameter of the input() is important. For example, the following shows how to convert fields of 20 chars, eg. '00000000000000012345' to numeric

	numericData = input(textData,20.); 

	numericData = sum(textData); 

Adding a row number in Proc SQL

In order to refer to the row number (also known as the observation number) of a record in a dataset within a proc sql, the mysterious monotonic function is used

proc sql;
	select monotonic() as N, course
        from en2008
; 
quit;

Importing long text fields from Excel

Use libname to import an Excel spreadsheet with really long fields, and note that the dbMax_text parameter is required to import fields longer than 1024 characters.

libname excelAGS excel "NiceSpreadsheet.xls" dbMax_text=5000;

DATA newDataSet;
     SET excelAGS.'Sheet1$'n (DBSASTYPE=(longFieldOne='char(2000)' longFieldTwo='char(2500)') );
run;

Condense

Please insert explanation here, Luke

Luke replies: sorry, I have no clue what this code is doing. Someone shoot me !

proc sort data=deet08.ld2008 out=stan1(keep=student course unit) nodupkey;
  by student course unit;
run;

data stan2;
  array u{30}  $  u1-u30;
  retain i u1-u30;
  set stan1;
  by student course ;

  if first.course then do;
    i=1;
    do k=1 to 30;
      u{k}='';
    end;
  end;
  else i=i+1;

  u{i}=unit;

  if last.course then output;
  drop i k unit;
run;

Calling R from SAS to produce a chart

R is better than SAS at producing charts. The example below shows how to call R from within SAS code, get R to use data within SAS to create a chart, then make that chart accessible to SAS. The chart is put into an RTF document, but there is also commented code which can be used to put it to an HTML file instead.

proc sql;
	create table jm_sas.fred as
	select crsetype_pct as name, n from fred;
quit;

DATA _NULL_;
FILE 'c:\temp\r\program.r' LRECL=1024;
PUT 'library(Hmisc)';
PUT 'library(RODBC)';
PUT "myConn <- odbcConnect('MySAS')";
PUT "myData <- sqlQuery(myConn, 'select * from JM_Sas.fred', believeNRows = FALSE)";
PUT "png(file='c:/temp/r/program.jpg')";
PUT "pie(myData$N,myData$name,init.angle=-60)";
PUT 'dev.off()';
PUT 'q()';
RUN;

OPTIONS XWAIT XSYNC;
X """C:\Program Files\R\R-2.8.1\bin\R.exe"" --no-save --quiet <""c:\temp\r\program.r"" >""c:\temp\r\program.log""";

/*proc sql;*/
/*	create table blah as */
/*	select * from jm_sas.fred;*/
/*quit;*/

DATA _NULL_;
	INFILE 'c:\temp\r\program.log';
	FILE LOG;
	INPUT;
	PUT '**R: ' _INFILE_;
RUN;

/*ODS ESCAPECHAR='^';*/
/*ODS HTML FILE='c:\temp\r\report.html' STYLE=minimal*/
/*GPATH='c:\temp\r' GTITLE GFOOTNOTE;*/

ODS ESCAPECHAR='^';
ODS RTF FILE='c:\temp\r\report.rtf' STYLE=minimal
GTITLE GFOOTNOTE;

/*DATA _NULL_;*/
/*FILE PRINT;*/
/*PUT "";*/
/*RUN;*/

DATA _NULL_;
FILE PRINT;
PUT "^S={PREIMAGE='c:\temp\r\program.jpg'}";
RUN;

ODS _ALL_ CLOSE;

The idea for this code came from "SAS to R to SAS" at Holland Numerics (direct link). However, that uses a CSV file to transfer data from SAS to R, whereas I have chosen to use ODBC because it's more sensible.

What is an Evening Flopper?

Here's a typical example -

They're often worn as a pair. Cf. l'espadrille en francais.

Getting current folder

In many SAS programs the location of imported and exported data is related to the location of the .sas script itself, and it is common to hard-code the location.

%let folder = C:\work\sas\enrolmentChecks\;

LIBNAME _dummy_ ".";

%let folder = %NRBQUOTE(%SYSFUNC(PATHNAME(_DUMMY_)));

/* Filename only */
proc sql noprint;
  select scan(xpath,-1,'\') into :progname 
  from sashelp.vextfl
  where upcase(xpath) like '%.SAS';
quit;

%put &progname; 

/* Full path */
proc sql noprint;
  select xpath into :progname
  from sashelp.vextfl
  where upcase(xpath) like '%.SAS';
quit;

%put &progname;

/* Script name */
%put %sysget(SAS_EXECFILENAME);

/* Folder name */
%let folder =  %qsubstr(%sysget(SAS_EXECFILEPATH),1
	,%length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname)));

Reading from Excel

A snazzy way to read data from excel is to create an excel library like so:

LIBNAME myxls "P:\stats\srstats\UWA student data.xls";
data students;
   set myxls.'Sheet1$'n;
run;

How dangerous are squirrels?

Very. Not only may they be carrying rabies, they may also be armed-

Adding student names to a dataset

We're commonly asked to produce reports containing student names, but our datasets only contain student numbers. The solution is to join with a table from the student system, like so:

proc sql;
	create table enrols_plus_names as
	select p.surname, p.given_names, en.*
	from deet11.en2011 en  /* our student enrolment dataset */
	inner join 
		simsprod.person p   /* the student system table on persons*/
		on input(en.student,8.0) = p.person_id
;quit;

Display Format Details

proc catalog c = library.formats;
  contents;
quit;

And to display contents of individual formats, one approach is:

proc format library=library;
  select $ps_course;
run;