PaperCC011
ExchangingdatabetweenSASandMicrosoftExcel
YuqingXiao,SouthernCompany,Atlanta,GA
ABSTRACT
[Link]
developedbydifferentgroupsofpeople,[Link]
availabletoday,onemaywonderWhichwouldfitmysituationthebest?Thispaperbrieflydiscusesseveraloptions
forimportingandexportingdatafrom/toMicrosoftExcelusingSASandtheirprosandcons.
INTRODUCTION
[Link]
and/orliketoreceivereports,[Link]
somemayrequiremultiplesheetswithmanyformattingandtablessomearesimplecreationofnewworkbooks
some [Link]
developedand/[Link] learning themare
[Link]/exportingdataandincludessome brief
discussionontheirusages.NotethatallSAScodeanddiscussionsarebasedonSAS9.1.3althoughtheyarenot
limitedtoit.
IMPORT/EXPORTDATAFROM/TOEXCEL
SomeofthecommonwaysofImport/Export:
DataStep
SASImport/ExportFacility
SAS/ACCESSLIBNAMEStatement
SAS/ACCESSPassThroughFacility
SASXMLEngine
DynamicDataExchange(DDE)
ODS
SASAddIntoMicrosoftOffice
DATASTEP
IfyouhaveasimpleonesheetExcelworkbook,[Link]
[Link].
[Link]:
Filenamemyfilec:\mywork\[Link]
Datasasdat
Infilemyfiledlm=,firstobs=2missover
Inputvar1var2$var3$var4
Run
[Link],and
youhavethecontrolovervariablenames,typesand/[Link]
SAS(additionalstepsmaybeneededtoFTPCSVfiletoyourUNIXbox).Someofthedrawbacksarethatitdoesnt
workwithmultiplesheets,youhavetosupplythevariablenamesand/or informatspecifications(althoughyoucan
writemoresophisticatedcodetoautomate thejob),datagetshiftedwhenthereareblankcells,plusyoulose
additionalinformationthatcanbestoredinXLSformatwhenyouconvertit.
Forexport,[Link]
writeCSVfile,[Link]
workbookswithformulas,pivottablesetc,butwritingXMLfilefromscratchisnotadauntingtasktoaverageSAS
users.A trickfordoingthisistocreatetheworkbookwithallthedesiredfeaturesandsaveinXMLformatfirst, then
openitwithtexteditorandcopy/pastethetextintoyourSASprogram,wrapeachlineinPUT,manuallydeal
[Link]
[Link].
1
Codeexampleforoutput .CSVfile:
Filenamemyfilec:\mywork\[Link]
Data_null_
setsasdat
filemyfiledelimiter=','dsd
if_n_=1thendo
put'name1'',''name2'',''name3'
end
putvar1var2$var3$var4
Run
[Link],youneedtooutputthemto
SASdatasetsinordertousethismethod.
SASIMPORT/EXPORTFACILITY
Anothereasywaytoimport/exportXLSfileistouseSASImport/[Link],gotoFileImport
data,chooseMicrosoftExcelappropriateversionfromdropdownlist,chooseworkbookandworksheetyoudlike
toreadin,[Link]
alsosaveacopyofImport/Export WizardgeneratedIMPORT/EXPORTprocedurecodeforsubsequentuseorwrite
[Link]/ExportwizardforXLSformatonlyavailableforWindowsandPROCIMPORT/EXPORTcandeal
withXLSfileinbothWindowsandUNIXenvironmentbutthe XLSfilehastoresideonPC.
SASIMPORTWizard:
CodeexampleforPROCIMPORT(Windows):
ProcImportdatafile="c:\mywork\[Link]"
out=[Link]=excelreplace
sheet="mydata$"
getnames=yes
mixed=no
scantext=yes
usedate=yes
scantime=yes
Run
CodeexampleforPROCIMPORT(UNIX):
ProcImportdatafile="c:\mywork\[Link]"
out=[Link]=excelcsreplace
server=market /*NameofPCfilesserver */
port=1234 /*PortnumberlisteningonthePCserver*/
version='2002'
sheet=mydata
scantext=yes
usedate=yes
scantime=yes
dbsaslabel=none
textsize=512
Run
2
CodeexampleforPROCEXPORT(UNIX):
Procexportdata=[Link]
outfile="c:\mywork\[Link]"dbms=excelcsreplace
sheet=mydata
version="2002"
server=market /*Servername*/
port=1234 /*Portnumber */
Run
[Link]
[Link]
disadvantagesarethatSAS/ACCESStoPCfilesisrequiredforimporting/exportingXLSformat,youhavelittle
controlovervariablenames,typesand/[Link]
[Link],theentireworksheethastobeimported,you
cantchoosecolumnsand/orranges.
ThePROCIMPORT/EXPORTactually imports/exportsXLSfilesthroughgeneratedSAS/[Link]
[Link],candefinecolumnsandrangestobereadin,can
changevariablenames,types, [Link],itsonlyavailableunderWindowsandmightbealittlebit
beyondbeginnerSASusersscope.
CodeexampleforPROCACCESS:
ProcAccessdbms=xls
/*createaccessdescriptor*/
creatework.dat_view.access
path="c:\mywork\[Link]"
worksheet=mydata
range='a1..j39'
getname=yes
scantype=5
mixed=yes
assign=no
renamevar1=name1
var2=name2
format1 5.0
2 $4
/*createSASview*/
creatework.dat_view.view
selectvar1var2var3
Run
/*createSASdataset*/
Datasasdat
setwork.dat_view
Run
[Link]/exporting
XLSfileexceptthatfilecanbesavedunderboth WindowsandUNIXenvironment.
Codeexampletoread/write .DBFfile:
ProcImportdatafile="c:\mywork\[Link]"
out=[Link]=dbfreplace
getdeleted=no
Run
Procexportdata=[Link]
outfile="c:\mywork\[Link]"dbms=dbfreplace
Run
AfewdrawbacksarethatseparateDBFfilesmaybeneededformultisheetworkbooks,oneneedstopayextra
attentiontohiddencolumnsand/ordatafilters [Link],allthe
formattingarelostafterconversion.
3
SAS/ACCESSLIBNAMESTATEMENT
SAS/ACCESSprovidesothermeansofaccessing/[Link]
alibraryreferencetoanExcelworkbookanddirectlyreadfromand/[Link],usepcfilesoption
withservernameandportname [Link]
[Link],suchasdata
step,SASSQLandSASProcedures.
CodeexampleforImport/ExportExcelfiles(Windows):
Libnamemyxls'c:\mywork\[Link]'
/*importdata*/
Datasasdat
[Link]
Run
/*exportExcelworkbook*/
Datamyxls.mydata2
setsasdat
Run
Procsql
createtablemyxls.summaryasselectvar1,var2,var3
fromsasdatgroupbyvar4
quit
Libnamemyxlsclear
CodeexampleforImportExcelfiles(UNIX):
Libnamemyxlspcfilesserver=marketport=1234path='c:\mywork\[Link]'
Datasasdat
setmyxls.'mydata$'n
Run
Libnamemyxlsclear
SAS/ACCESSPASSTHROUGHFACILITY
AnalternativetoSAS/[Link]/ACCESStoconnectto
[Link]
dataorasubsetofthedata fromExcelworkbookworksheetsand/[Link]
WindowsandUNIXenvironment.
CodeexampleforSAS/ACCESSPassThrough(Windows):
Procsql
connecttoexcel(path='c:\mywork\[Link]')
createtablesasdatasselect*fromconnectiontoexcel
(selectvar1,var2,var3frommydata)
disconnectfromexcel
Quit
CodeexampleforSAS/ACCESSPassThrough(UNIX):
Procsql
connecttopcfiles(path='c:\mywork\[Link]'server=marketport=1234)
createtablesasdatasselect*fromconnectiontopcfiles
(select*frommydata)
disconnectfrompcfiles
Quit
SASXMLENGINE
[Link] translatesdatabetweenXMLdocumentandSASdata
[Link],withoutfurtherinstructions
[Link],youcancreateanXMLMapwhichtellsthe
[Link]
[Link]
primarypaneandcreateXMLMAPbydraganddropelementsfromXMLprimarypaneintoXMLMAPprimarypane,
thenspecifytheXMLMAPintheLIBNAMEstatementtotranslatetheXMLdocument.
4
SASXMLMapper:
CodeexampleforSASXMLengine:
/*assignlibreftoXMLdocumentlocationandspecifyXMLengine*/
Filenamemymap'c:\mywork\[Link]'
Libnamemypathxml'c:\mywork\[Link]'xmlmap=mymap
/*readinXMLdocumentwithdatastep*/
datasasdat
[Link]
run
/*readinXMLdocumentwithprocedures*/
proccopyin=mypathout=sasdat
selectgrades
run
/*createXMLdocumentwithdatastep*/
[Link]
setsasdat
Run
[Link]
independentcharacteristicmakesitveryusefulinsharingdatabetweendifferentpartiesandrelativelyimmuneto
[Link],[Link], inExcel,
[Link]
saveasXMLspreadsheet,theresultingfilestructurecanbe complicateandIhaventhadanysuccessbuildingXML
[Link],[Link],XML
[Link].
DYNAMICDATAEXCHANGE(DDE)
[Link]
spreadsheetsandSAS [Link],MicrosoftExcel
[Link]
[Link].
Tripletconsistsofthreepartsintheformapplicationname|topic!item,whereapplicationisExcelinthiscase,topic
[Link]
tripletistocopythedesiredrangeofcellsto theclipboardandtheninPCSAS,gotoSolutions>Accessories>DDE
[Link] last,you
maywanttoclosetheworkbookandExcelapplication.
[Link] filethroughDDE:
/*invokeExcelandopenworkbook*/
Optionsnoxwaitnoxsync
x'"c:\programfiles\microsoftoffice\office11\[Link]"'
Data_null_
rc=sleep(5)
Run
5
Filenameddecmddde'excel|system'
Data_null_
fileddecmd
put'[FILEOPEN("c:\mywork\[Link]")]'
Run
/*readdesiredrowsandcolumnsfromExcelfileintoSAS*/
Filenamemyfiledde'excel|c:\mywork\[[Link]]mydata!r2c1:r10c3'
Datasasdat
infilemyfilenotabdlm=09xdsdmissover
informatvar15.var2$4.var3$20.
inputvar1var2$var3$
Run
/*closeworkbookandquitExcel*/
Data_null_
filexlin
put'[FILECLOSE("c:\mywork\[Link]")]'
put'[QUIT()]'
Run
[Link]:
/*invokeExcelandopennewworkbook*/
Optionsnoxwaitnoxsync
x'"c:\programfiles\microsoftoffice\office11\[Link]"'
Data_null_
rc=sleep(5)
Run
/*saveblankspreadsheettodesiredlocation*/
Filenameddecmddde'excel|system'
Data_null_
fileddecmd
put'[[Link]("c:\mywork\[Link]")]'
Run
/*definefilerefusingDDEaccessmethod*/
Filenamemyfiledde'excel|c:\mywork\[[Link]]sheet1!r1c1:r&rows.c3'notab
/*writetoExcelworkbook*/
Data_null_
filemyfile
[Link]
if_n_=1thenput'name1''09'x'name2''09'x'name3'
putvar109xvar209xvar3
Run
/*saveworkbookandquitExcel*/
Filenameddecmddde'excel|system'
Data_null_
fileddecmd
put'[SAVE()]'
put'[QUIT()]'
Run
DDEgivesyoutheflexibilityofspecifyingthedesiredrangesforread/writeandcontrolovervariabletypesandlength.
Inaddition,youcanissueExcelcommandstocustomizethe spreadsheet,likesetformat,font,color,header/footeror
runmacros,[Link]
[Link]
[Link],youhavetoknowExcelcommandstodoformatting.
ODS
[Link]
withhighlycustomizedformatting. ODSorganizesoutputfromdatastepsorproceduresintoaseriesofobjectsand
[Link]
begenerated,suchasHTML,XML,[Link],[Link]
createmultisheetExcelworkbooks,[Link]/ordata
6
[Link]
yourownstyledefinitionormodifyanexistingone throughPROCTEMPLATE. Withcustomstyledefinitions,SAS
ODScangenerateverysophisticatedExcelworkbookwithformulas,highlighting,wrappedtextandmuchmore.
SamplecodeforcreatingmultisheetspreadsheetwithODS:
/*closeodslistingdestination*/
Odslistingclose
/*opentagsetsdestinationandsendoutputtoxmlfile*/
[Link]='c:\mywork'file='[Link]'style=mystyle
[Link](sheet_name=mydata)
Data_null_
setsasdat
fileprintods=(variables=(var1var2var3))
put_ods_
Run
[Link](sheet_name=summary)
OdsExcludeMomentsTestForLocation
Procunivariatedata=sasdat
Byvar2
varvar1
Run
/*closetagsetsdestinationandreopenlistingdestination*/
[Link]
Odslisting
ODSisanexcellentwayofexportingSASdatasetstoExcelworkbooksandcanmakefancylookingExcel
[Link]
possibledrawbackisthatexploringmanystyleoptionsforcustomizedformattingcanbetimeconsumingandmaynot
beyourbestchoicewhenmovingdataisthecentralfocus.
SASADDINTOMICROSOFTOFFICE
SASAddInforMicrosoftOfficeismynewfavoritemethodofsharingdatabetweenSASandMicrosoftOffice
[Link]
[Link],youcanopenSASdatasetinExcel,viewand/oreditdata,analyzethedataanddo
[Link]
SASdataset,findSASmenuinthetoolbar,selectSASOpenDataSourceIntoWorksheet,chooseyourSAS
[Link]
[Link],selectthedesiredrangeofdata,gotoSASActive
DataCopytoSASServer,usebrowsertofindthelocationwhereyoudliketosavethedata,name thedataset
thensave.
SASMenuinMicrosoftExcel:
[Link],SASAddInforMicrosoftOfficemustconnecttoan
[Link], thedatafoldermustberegisteredwith
[Link]
recordsatOptionswindowunderSASmenuitem.
CONCLUSION
Sofar,[Link] pros
[Link]
[Link],evenmore [Link] macroslike%sas2xls,
7
%[Link]%xlxp2sas,dataconversionsoftware,orsolutionswithotherlanguageslikeXML,VSTOandVBA
etc.
SummaryofDataExchangingMethods:
Available Available Workwith Excel OtherSAS
Import Export Workwith
withPC withUNIX Multisheet Formatting packages
capable capable procedures
SAS SAS workbook available required
DataStep
Import/Export
SAS/ACCESS
Wizard
Import/Export
SAS/ACCESS
procedure
ProcAccess SAS/ACCESS
SAS/ACCESS
SAS/ACCESS
LIBNAME
SAS/ACCESS
SAS/ACCESS
PassThrough
SASXML
Engine
DDE
ODS
SASAddIn SASBI
REFERENCES
1. SASOnlineDOC,SASInstituteInc.
<[Link]
2. ImportingExcelfilestoSASDatasets.
<[Link]
3. [Link]
<[Link]
4. LawrenceHelbers,AlexVinokurov. SASOutputtoExcel:DDEandBeyond.NESUG2002Conference.
5. [Link].NESUG2004Conference.
6. [Link].
SUGI2006Conference,paper11531.
7. [Link].7Apr.2005. SASL
<[Link]
8. [Link].23Jan.2006.
<[Link]
ACKNOWLEDGMENTS
IwouldliketothankmycolleaguesBobBolen,ChaoyingHsiehandDianCunninghamfortheirthoroughreviewand
valuablecomments.
CONTACTINFORMATION
[Link]:
YuqingXiao
SouthernCompanyMarketingServices
Bin10206
[Link]
Atlanta,GA30308
WorkPhone: (404)5064619
Email: yxiao@[Link]
[Link]
[Link].
Otherbrandandproductnamesaretrademarksoftheirrespectivecompanies.
