22S:166Computing in StatisticsOther Software PackagesProc importA bit on SAS macro languageLecture 21Nov. 28, 2007Kate Cowles374 SH, [email protected] software packages• Microsoft Excel– spreadsheet– very convenient for entering data in flat-file format– clients very frequently bring data to statis-ticians in Excel format– NOT reliable and accurate for doing sta-tistical analysis• Microsoft Access– relational da ta base management systemReading data files into SAS fromother software packages• Import Wizard– point-and-cli ck interactive reading– convenient if file only needs to be read once– can write proc import code to be copiedinto programs• proc import– can be used instead of data step in SASprograms– much more convenient if file needs to beread in multiple programs, or program us-ing file needs to be run repeatedlyImporting from Other SourcesTypes of files that the Import Wizard and/or procimport can readIdentifier Input D at a Sour ce ExtensionACCESS Microsoft Access database .MDBDBF dBASE file .DBFWK1 Lotus 1 spreadsheet .WK1WK3 Lotus 3 spreadsheet .WK3WK4 Lotus 4 spreadsheet .WK4EXCEL Excel V 4 or 5 spreadsheet .XLSEXCEL4 Excel V 4 spreadsheet .XLSEXCEL5 Excel V 5 spreadsheet .XLSEXCEL97 Excel 97 spreadsheet .XLSDLM delimited file (default is blank) .*CSV delimited file (comma-sep vals) .CSVTAB delimited file (tab-delimited ) .TXTRestriction: The data sources available to you dependon the SAS/ACCESS product s that you have licensed.If you do not have any SAS/ACCESS products licensed,then the only types of data source files available to youare .CSV, .TXT, and d elimited files.Example• from R or Splus– use write.table to write data o ut as a delimitedfileData frame that comes with R> USArrestsMurder Assault UrbanPop RapeAlabama 13.2 236 58 21.2Alaska 10.0 263 48 44.5Arizona 8.1 294 80 31.0Arkansas 8.8 190 50 19.5California 9.0 276 91 40.6...R command to write out file as tab-delimiteddata file> write.table( USArrests, file="C:\\My Documents\\166\\USArrests.txt",sep="\t", quote = FALSE, col.names=TRUE)Now in SAS....File / Import DataImport WizardSelect a data source from the list belowChoose "Delimited File (*.*)"Where is the file located?Give full path name, e.g.C:\My Documents\166\USArrests.txtChoose SAS destination:Library: (defaults to WORK)Member: (fill in name of your choice; e.g. USArrest)Question as to whether you want wizard to generate procimport statements so you can just run them next timeWhat it generatedPROC IMPORT OUT= WORK.usarrestDATAFILE= "C:\My Documents\166\USArrests.txt"DBMS=DLM REPLACE;DELIMITER=’00’x; * needed correction to DELMITED=’09’x ;GETNAMES=YES;DATAROW=2;RUN;Example of reading Access databasePROC IMPORT OUT= WORK.coursesDATATABLE= "Courses"DBMS=ACCESS97 REPLACE;DATABASE="c:\my documents\166\univ0_v7";RUN;Overview of SAS Macro Programming• purpose is to make SAS programming more efficientand to reduce coding errors• macro variables– enable substitution of text into SAS programs• macro programs– enable performing the same task on different inputswithout rewriting codeExample datasetData Set Name: BOOKS.YTDSALES Observations: 6959Member Type: DATA Variables: 10Engine: V8 Indexes: 0Created: 7:36 Friday, October 19, 2001 Observation Length: 216Last Modified: 7:36 Friday, October 19, 2001 Deleted Observations: 0Protection: Compressed: NOData Set Type: Sorted: NOLabel:-----Alphabetic List of Variables and Attributes-----# Variable Type Len Pos Format Informat Label6 author Char 50 115 First Author8 cost Num 8 8 DOLLAR9.2 Wholesale Cost4 datesold Num 4 32 MMDDYY8. MMDDYY8. Date Book Sold9 listpric Num 8 16 DOLLAR9.2 List Price7 publishr Char 50 165 Publisher2 saleid Num 8 0 8. Sale ID3 saleinit Char 3 62 Sales Person Initials10 salepric Num 8 24 DOLLAR9.2 Sale Price1 section Char 26 36 Section5 title Char 50 65Macro variables• %let keyword defines a macro varia ble andassigns it a value• use & before macro variable name when ref-erencing variable• use %eval keyword to convert a macro vari-able’s value to numeric• when referencing macro variables in charac-ter literal s, use double quotesMacro variables example%let repmonth=4;%let repyear=2001;%let repmword=%sysfunc(mdy(&repmonth,1,&repyear),monname9.);data month&repmonth;set books.ytdsales;mosale=month(datesold);label mosale=’Month of Sale’;run;proc tabulate data=month&repmonth;title "Sales During &repmword &repyear";where mosale=&repmonth and year(datesold)=&repyear;class section;var salepric listpric cost;tables section all=’**TOTAL**’,(salepric listpric cost)*(n*f=4. sum*f=dollar9.2);run;* proc gchart data=month&repmonth ;proc chart data=month&repmonth(where=(mosale < %eval(&repmonth+1) andyear(datesold)=&repyear));title "Sales Through &repmword &repyear";pie section / sumvar=salepric noheading ;run;OutputSales During April 2001 1---------------------------------------------------------------| | Sale Price | List Price |Wholesale Cost|| |--------------+--------------+--------------|| | N | Sum | N | Sum | N | Sum ||----------------+----+---------+----+---------+----+---------||Section | | | | | | ||----------------| | | | | | ||Internet | 145|$4,579.71| 145|$4,680.75| 145|$3,318.77||----------------+----+---------+----+---------+----+---------||Networks and | | | | | | ||Communication | 55|$1,633.01| 55|$1,665.25| 55|$1,177.46||----------------+----+---------+----+---------+----+---------||Operating | | | | | | ||Systems | 132|$4,016.45| 132|$4,108.40| 132|$2,916.03||----------------+----+---------+----+---------+----+---------||Programming | | | | | | ||Languages | 60|$1,835.07| 60|$1,878.00| 60|$1,330.98||----------------+----+---------+----+---------+----+---------||Web Design | 131|$4,015.50| 131|$4,114.45| 131|$2,910.87||----------------+----+---------+----+---------+----+---------||**TOTAL** | 523|$16079.73| 523|$16446.85| 523|$11654.09|---------------------------------------------------------------Sales Through April 2001 18Networks and Com************** . ***** . ** Internet** .$5725.76 *** .9.54%. *** . . 14911.01 **** . . 24.84% *** . . ** .. . *Operating System * 16660.07 ** 27.75% . . .. . .. . ** . ** . . ** .. . *** . . 15555.92 **** $7178.46 . 25.91% *** .. 11.96% . *** . **** . ** Web Design*** . ***Programming Lang ***********Using built-in SAS macro variablestitle "Sales Report";title2 "As of &systime &sysday &sysdate";title3 "Using SAS Version: &sysver";proc means data=books.ytdsales n sum;var
View Full Document
Unlocking...