February, 2008
by Phil Mason
ABSTRACT
SAS® 9 has many new features, as well as all the old ones. This paper is the third part of a three part series which will outline some of the best new features that I have found over the last 3 years that I have been using SAS 9. In the first part I discussed the Data Step Features. The second part covered the ODS and Graph Features. In this part I will discuss Macro, SQL, and some miscellaneous features.
Introduction
The paper is based on a series of tips & techniques that I publish to a list of subscribers to my newsletter (see www.woodstreet.org.uk). This being a hands on workshop means that I will outline the feature we are looking at and then we will follow through some example code to see how the feature works and what can be varied. Remember that the tips & techniques covered here are only a small subset of everything that is available in SAS 9. You should make sure that you read the What’s New documentation for the version of SAS 9 that you have. And don’t forget to always read the changes and enhancements for each new service pack you get too.
Macro, sql, and miscellaneous Features
Putting numbers in macro variables - a better way
In SAS 9 there is a new function called SYMPUTX which creates a macro variable from a numeric variable, without writing a note to the log, and trims leading & trailing blanks. It will use a field up to 32 characters wide and you can optionally tell it which symbol table to put the macro variable into.
The example below show how SAS does an automatic type conversion and uses BEST12.2 to convert a numeric to character in line 44. The following line shows how we can explicitly do the conversion and trim the result. The next line shows how to use SYMPUTX to simplify the process.
SAS Log
42 data test ;
43 my_val=12345 ;
44 call symput('value0',my_val) ; * auto conversion done ;
45 call symput('value1',trim(left(put(my_val,8.)))) ; * v8 ;
46 call symputx('value2',my_val) ; * SAS 9 ;
47 run ;
NOTE: Numeric values have been converted to character values at the places given by:
(Line):(Column).
44:24
NOTE: The data set WORK.TEST has 1 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
48 %put =->&value0<-==->&value1<-==->&value2<-=;
=-> 12345<-==->12345<-==->12345<-=
Creating a range of macro variables from SQL with leading zeroes
In versions of SAS prior to SAS 9 you could create ranges of macro variables, but could not use leading zeroes in the macro variable names. So in 8.2 you might end up with macro variables like var8, var9, var10. But in SAS 9 you could create variables like var08, var09, var10. This can make further use of those macro variables easier to code.
SAS Program
proc sql noprint ;
select name into :name01-:name19 from sashelp.class ;
Use regular expressions in SQL
Chris Brooks (Office of National Statistics - UK) pointed out to me that you can use regular expressions within SQL. This can be quite powerful in selecting data that matches certain conditions. The following example shows a simple regular expression which selects only quarterly periods from a table containing years, quarters & months.
Program
length period $ 7 ;
input period ;
cards ;
2005
2005Q1
2005JAN
;;
run ;
proc sql;
create table qtrs as
select *
from test
where prxmatch("/\d\d\d\d[qQ][1-4]/",period) ;
quit;
proc print data=qtrs ;
run ;
Log
3189 data test ;
3190 length period $ 7 ;
3191 input period ;
3192 cards ;
NOTE: The data set WORK.TEST has 3 observations and 1 variables.
NOTE: DATA statement used (Total process time):
real time 1.10 seconds
cpu time 0.03 seconds
3196 ;;
3197 run ;
3198 proc sql;
3199 create table qtrs as
3200 select *
3201 from test
3202 where prxmatch("/\d\d\d\d[qQ][1-4]/",period) ;
NOTE: Table WORK.QTRS created, with 1 rows and 1 columns.
3203 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.95 seconds
cpu time 0.04 seconds
3204 proc print data=qtrs ;
3205 run ;
NOTE: There were 1 observations read from the data set WORK.QTRS.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.70 seconds
cpu time 0.03 seconds
Output
Obs period
1 2005Q1
Bonus Quick Tips
Count
Count up the number of substrings in a string.
data _null_ ;
long='a b c d a e d a e t g d a c s' ;
num_a=countc(long,'a') ;
put num_a= ;
run ;
Countc
Count up the number of characters in a string.
data _null_ ;
long='dog cat rat bat dog camel dingo snake bigdog' ;
num_dog=count(long,'dog') ;
put num_dog= ;
run ;
CHOOSEC & CHOOSEN
Choose the nth element from a list of items, either numeric or character.
data _null_ ;
third=choosec(3,'dog','cat','rat','bat') ;
put third= ;
last=choosec(-1,'dog','cat','rat','bat') ;
put last= ;
run ;
Some more worth looking at
· FIND & FINDC
· IFC & IFN
· STRIP
· COALESCE & COALESEC
· FILENAME fileref CLIPBOARD ;
· PROC PROTO & PROC FCMP
· PROC SYLK
· PROC EXPORT SHEET=
· %SYMEXIST
· LIBNAME libref META
· _NEW_
· OPTION MPRINTNEST
Create a pivot table from SAS
A colleague and I were looking at the best way to create an EXCEL pivot table automatically from SAS. We considered solutions such as ODS with MSO XML directives, straight XML, DDE, and so on – but these were all very complex. He finally came up with the following simple method.
We use a SAS program to create a spreadsheet and then call a Visual Basic Script. The Visual Basic Script does the following:
· open the spreadsheet
· add a new sheet for pivot table
· create a pivot table using wizard
· set the fields to be used in the table
The SAS program could be extended to make a macro which creates the VBS file. This could then make it parameter driven to work for all data.
SAS Program
* create EXCEL spreadsheet ;
proc export data=sashelp.class
outfile="c:\sas\class.xls"
dbms=excel;
quit;
* call VB script to make the pivot table ;
data _null_;
x 'c:\sas\pivot.vbs';
run;
VB Script Program
Set XL = CreateObject("Excel.Application")
XL.Visible=True
XL.Workbooks.Open "c:\sas\class.xls"
Xllastcell= xl.cells.specialcells(11).address
XL.Sheets.Add.name = "PivotTable"
xldata="class"
XL.Sheets(xldata).select
XL.ActiveSheet.PivotTableWizard SourceType=xlDatabase,XL.Range("A1" & ":" & xllastcell),"Pivottable!R1C1",xldata
XL.ActiveSheet.PivotTables(xldata).PivotFields("Name").Orientation = 1
XL.ActiveSheet.PivotTables(xldata).PivotFields("Age").Orientation = 1
XL.ActiveSheet.PivotTables(xldata).PivotFields("Sex").Orientation = 1
XL.ActiveSheet.PivotTables(xldata).PivotFields("Height").Orientation = 4
XL.ActiveWorkbook.ShowPivotTableFieldList = False
Browsing metatdata from SAS
From SAS 9 you can browse through metadata via the Explorer Pane (if you have metadata via a metadata sever defined). To enable this follow these steps:
1. Start SAS 9
2. Click on the Explorer pane to activate it or use the menu VIEW/CONTENTS ONLY.
3. Now select TOOLS/OPTIONS/EXPLORER
4. Under the GENERAL tab select the METADATA SERVERS check box, and then click on OK.
5. There will now be a new METADATA SERVERS icon in the Explorer pane. Click on this to show its contents. There will be nothing there yet.
6. Right click in the explorer pane and select NEW. This allows you to define a connection to a metadata server – which may be on your machine, or elsewhere. Define this and any others you may want.
7. Now you can continue to drill down through the metadata to examine it.
Exporting data to Microsoft Access via XML
If you have SAS/Access for PC File formats, then you can use that to send your data between SAS & Access. However you can use an alternative method from Base SAS to export data to Access. You can use the XML engine on the libname statement, along with xmltype=msaccess, which creates XML specifically for Access. By also specifying “xmlmeta=schemadata” you will get the variable attributes included in the XML and imported by access too. Finally you import the XML file from within Access by using “File”, “Get external data”. Note: index creation is not supported.
Sample Program
libname access xml 'd:\test.xml'
xmltype=msaccess xmlmeta=schemadata;
data test(index=(year month)) ;
set sashelp.retail ;
run ;
proc copy in=work out=access index=yes ;
select test ;
run ;
libname access ;
Log
1 libname access xml 'd:\test.xml'
2 xmltype=msaccess xmlmeta=schemadata;
NOTE: Libref ACCESS was successfully assigned as follows:
Engine: XML
Physical Name: d:\test.xml
3 data test(index=(year month)) ;
4 set sashelp.retail ;
5 run ;
NOTE: There were 58 observations read from the data set SASHELP.RETAIL.
NOTE: The data set WORK.TEST has 58 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.68 seconds
cpu time 0.06 seconds
6 proc copy in=work out=access index=yes ;
7 select test ;
8 run ;
NOTE: Copying WORK.TEST to ACCESS.TEST (memtype=DATA).
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
WARNING: Indexes for ACCESS.TEST.DATA cannot be created.
WARNING: Engine XML does not support index create operations.
NOTE: There were 58 observations read from the data set WORK.TEST.
NOTE: The data set ACCESS.TEST has 58 observations and 5 variables.
NOTE: PROCEDURE COPY used (Total process time):
real time 1.54 seconds
cpu time 0.23 seconds
9 libname access ;
NOTE: Libref ACCESS has been deassigned.
Export to EXCEL via libname
In SAS 9 you can now read & write EXCEL spreadsheets from data steps and procedures. This greatly simplifies exporting data to EXCEL. The following log shows how I created an EXCEL file called test.xls, with a sheet called CLASS. The sheet lists variable names in the first row, followed by values on subsequent rows. Note the error message demonstrating that there are some limitations with the EXCEL engine, preventing me from overwriting a sheet once I have created it. Following that you can see that I can create more sheets within the file.
SAS Log
41 libname out excel 'c:\test.xls' ;
NOTE: Libref OUT was successfully assigned as follows:
Engine: EXCEL
Physical Name: c:\test.xls
42 data out.class ; set sashelp.class ; run ;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set OUT.class has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds
43 * try to replace dataset ;
44 data out.class ; set sashelp.class ; run ;
ERROR: The MS Excel table class has been opened for OUTPUT. This table already
exists, or there is a name conflict with an existing object. This table
will not be replaced. This engine does not support the REPLACE option.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds
45 * make a new dataset ;
46 data out.shoes ; set sashelp.shoes ; run ;
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 395 observations read from the data set SASHELP.SHOES.
NOTE: The data set OUT.shoes has 395 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.02 seconds
47 * free it so we can read the spreadsheet from EXCEL ;
48 libname out ;
NOTE: Libref OUT has been deassigned.
Using the ZIP engine to read zip files
There is a currently undocumented filename engine available in SAS 9 that can be used to read from compressed ZIP files directly. You simply specify the engine “SASZIPAM” on a filename statement, and when referring to it you must specify the file within it that you wish to read. In the example below “tomcat.zip” contains a number of files. I want to read “tomat.log” and therefore specify “in(tomcat.log)”, where “in” is the libref and “tomcat.log” is the file I will read from the zip file.
Sample SAS Program
filename in saszipam 'c:\tomcat.zip';
data _null_;
infile in(tomcat.log);
input ;
put _infile_;
if _n_>10 then
stop ;
run;
Sample SAS Log
4 filename in saszipam 'c:\tomcat.zip';
5 data _null_;
6 infile in(tomcat.log);
7 input ;
8 put _infile_;
9 if _n_>10 then
10 stop ;
11 run;
NOTE: The infile library IN is:
Stream=c:\tomcat.zip
NOTE: The infile IN(tomcat.log) is:
File Name=tomcat.log,
Compressed Size=1894,Uncompressed Size=15793,
Compression Level=-1,Clear Text=Yes
Using CATALINA_BASE: C:\Tomcat4.1
Using CATALINA_HOME: C:\Tomcat4.1
Using CATALINA_TMPDIR: C:\Tomcat4.1\temp
Using JAVA_HOME: C:\j2sdk1.4.2_04
Using Security Manager
Starting service Tomcat-Standalone
Apache Tomcat/4.1.18
INFO: System properties were read from a file.
This discovery service deployment looks up remote services.
------------------------------------------------------------------
NOTE: A total of 11 records were read from the infile library IN.
NOTE: 11 records were read from the infile IN(tomcat.log).
NOTE: DATA statement used (Total process time):
real time 0.78 seconds
cpu time 0.06 seconds
Exporting to EXCEL
If you have “SAS/Access for PC File Formats” licensed it is now very easy to import and export data between SAS and Microsoft EXCEL. You can reference EXCEL spreadsheets directly with a libname statement – no engine is required. You can then refer to a spreadsheet using the libref and a worksheet by using a “dataset name”. For instance in the example below, nice.test refers to the spreadsheet ‘c:\nice.xls’ and within it the worksheet called ‘test’.
SAS Log
11 libname nice 'c:\nice.xls' ;
NOTE: Libref NICE was successfully assigned as follows:
Engine: EXCEL
Physical Name: c:\nice.xls
12 data nice.test ;
13 set sashelp.class ;
14 run ;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The data set NICE.test has 19 observations and 5 variables.
NOTE: DATA statement used (Total process time):
real time 0.04 seconds
cpu time 0.02 seconds
15 libname nice ;
Bonus Quick Tips
SAS Web Infrastructure Kit
If you have SAS Integration Technologies, then make sure you know what this is since it is the most useful thing to come from SAS in the last 10 years (in my opinion). Why? This is what makes it possible to write a SAS program and make it instantly accessible to be run via a web browser. Supports full security protocols. If you have Enterprise Guide you can even use a wizard to write your program, convert to a stored process and generate a web page to let users run it.
Some more worth looking at
· Pipeline parallelism in SAS/Connect
· PROC TIMESERIES in SAS/ETS
·
CONCLUSION
In this paper I have described just a few of the many new features available in SAS version 9. To get a complete list of all the new features review the “What’s New in SAS” documentation on the SAS Institute web site.
Contact Information
Your comments and questions are valued and encouraged. Contact the author at:
Phil Mason
Wood Street Consultants Ltd.
16 Wood Street
Wallingford, Oxfordshire, OX10 0AY, ENGLAND
E-mail: phil@woodstreet.org.uk
Web: www.woodstreet.org.uk
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.
Other brand and product names are trademarks of their respective companies.