Access denied |
used Cloudflare to restrict access
Please enable cookies.
What happened?
The owner of this website () has banned your access based on your browser's signature (39f82-ua98).pc files server & SAS博客列表
I have a love-hate relationship with spreadsheet data.
I am interested in finding data to analyze, and if it arrives in the form of a spreadsheet, I'll take it.
And I like to deliver results and reports, but often my constituents ask for it as a spreadsheet that they can then manipulate further.
A spreadsheet is not a database, so it can be a challenge to whip spreadsheet data into analysis-ready shape.
Likewise, saving results as a spreadsheet can lose something in the translation -- usually value formatting, appearance attributes, or graphs.
SAS offers many ways to read from and write to Microsoft Excel spreadsheets.
They each have pros and cons.
This post is an inventory of the methods that I know about for creating Excel files from within SAS.
Some "bits" about 32-bit and 64-bit architecture
Before I get to the Big List, let me set the stage by describing a few terms and concepts.
In order to create Excel files directly from SAS, you need .
This product enables all sorts of file exchanges between SAS and other PC-based applications, including Microsoft Excel, Microsoft Access, , and more.
SAS/ACCESS to PC Files includes a component called the .
The PC Files Server is a service-based application that runs apart from SAS on a Windows node.
It accepts requests from SAS to convert data to and from Excel (and other file types).
Traditionally, this innovation allows SAS running on UNIX (where there are no native facilities for Excel data) to exchange data with PC-based data programs.
Recent changes in desktop computing have complicated the picture.
Newer desktop machines all have 64-bit architecture, and most organizations are now adopting 64-bit versions of Microsoft Windows.
All of your 32-bit applications (designed for x86 architecture) still can still run, of course, but there are a few hard-and-fast rules.
One of those rules is that a .
And guess what?
There is a better-than-even chance that the built-in data providers that you have for Microsoft Excel -- the bits that allow SAS to write to Excel on Windows -- are 32-bit modules.
This means that the PROC EXPORT DBMS=EXCEL program that worked in your 32-bit SAS session .
There are two remedies for this bitness mismatch.
First, you could install the 64-bit data providers (which accompany the 64-bit version of Microsoft Office).
But you c if you have 32-bit Microsoft Office, then you're stuck with the 32-bit providers for now.
The second remedy is to use the PC Files Server, right there on the same Windows machine where SAS is running.
This allows a 64-bit SAS process to delegate the data exchange to a 32-bit PC Files Server process.
Thanks to the out-of-process communication, this circumvents the bit architecture mismatch.
To make this work you don't have to set up any additional software, but your SAS programs must change to use .
The EXCELCS keyword tells SAS to use the PC Files Server instead of attempting to use in-process data providers.
Exporting to Excel: ways to get there from SAS
With the architecture lesson behind us, here's my list for how to put SAS content into Microsoft Excel.
I won't dive into much detail ab you can follow the links to find more documentation.
These methods use features of SAS/ACCESS to PC Files:
– reads/writes Excel files at the sheet level when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office).
An Excel file is viewed as a SAS library, while sheets/ranges are the member tables.
Requires exclusive lock on an existing Excel file.
– does the same as LIBNAME EXCEL, but uses PC Files Server.
Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.
– uses PC Files Server to write an Excel file.
There are various options to control the output behavior.
Good for UNIX and for Windows configurations where bitness of SAS and Microsoft Office don't match.
- writes Excel files when the bitness of SAS (32- or 64-bit) matches the bitness of Microsoft Office installed (or more specifically, the ACE drivers that accompany Office).
– writes Excel (XLS) files directly, no driver or PC Files Server needed.
Has limits on volume and format.
Works on Windows and UNIX.
– new in 9.3M1, writes Excel 2010 files (XLSX format) directly.
No driver or PC Files Server needed.
Works on Windows and UNIX.
The following methods do not require SAS/ACCESS to PC Files, so they are popular, even if they don't produce "native" Excel files:
– produces comma separated value files, most often used in Excel.
(or just DATA step and FILE output) – produces comma separated value files, most often used in Excel.
– uses ODS to create an Office XML file.
Provides a fair amount of control over the content appearance, but recent versions of Excel do not recognize as a "native" format, so user is presented with a message to that effect when opening in Excel.
– uses Windows messages to control what goes into an Excel file, down to the cell level.
Works only when SAS for Windows and Excel are on the same machine.
Doesn't work in SAS workspace servers or stored process servers (often accessed with SAS Enterprise Guide).
It's an antiquated approach, but offers tremendous control that many long-time SAS users enjoy.
SAS client applications make it easier
While I've focused on the SAS programming methods for creating Excel files, applications like
make the operation a breeze.
SAS Enterprise Guide can import and export Excel files through point-and-click methods, and SAS/ACCESS to PC Files is not needed to make that work.
(However, the work is not captured in a SAS program, so it cannot be run in SAS batch jobs or stored processes.)
SAS Add-In for Microsoft Office turns the problem on its head.
By allowing you to access SAS data and analytics from within Microsoft Excel, you pull the results into your Excel session, rather than export them from your SAS session.
In case you missed it, the .
Because we're all friends here, you may call it "SAS 9.3M1" for short.
Maintenance releases are
that SAS has found or that customers have reported.
However, sometimes a new capability might sneak out the door along with it.
That's what happened this time with improved XLSX support in PROC EXPORT.
Now for the first time, this round-trip import/export cycle works just as you would expect with no additional setup required:
proc export
data=sashelp.prdsale
outfile=&c:\temp\prdsale.xlsx&
proc import
datafile=&c:\temp\prdsale.xlsx&
out=work.prdsale
Remember: using the IMPORT and EXPORT procedures to read and write Microsoft Excel files requires the
With these latest changes, you can get the job done without setting up a , even on systems that don't have a 32-bit Windows architecture.
Prior to this support, most customers who run SAS for 64-bit Windows or on a UNIX platform would need to use DBMS=EXCELCS for this operation.
The EXCELCS method works by delegating the Excel read/write operation to .
(On 64-bit Windows, you can make it work with very little configuration using .)
The PC Files Server is still very useful for other scenarios, such as supporting the , which can read and write Microsoft Excel and Microsoft Access files, among others.
And if you're looking for a point-and-click method for , we cracked that nut a while ago.
I hope that the following statement is not too controversial...but here it goes: Microsoft Excel is not a database system.
That is, I know that people do use it as a database, but it's not an application that supports the rigor and discipline of managing data in the same way that "real" database systems do.
Even Excel's more boring and less popular cousin, Microsoft Access, supports the discipline of database management much better than Excel.
Still, Excel spreadsheets are widely used because they are so flexible and can contain rich content, including charts and fancy formatting.
These might be assets when it comes to creating compelling output, but it doesn't help a bit when your task is to analyze the data.
That's why the first step to analyzing spreadsheet data within SAS is to import the data into a SAS data set.
The Import process allows you to impose the oft-needed discipline to the spreadsheet data "on the way in" to your SAS environment.
For example, you can:
Select the data sheet, or an exact range within a sheet, that you want to import
Apply formatting rules to the data values so that they are treated appropriately (as character, numeric, date or time values, for example)
Control the field names so that you have SAS-friendly variable names in the resulting data set
Done properly, these steps will reduce the amount of "clean up" you need to perform before you can proceed with analysis.
The Import Data task within SAS Enterprise Guide (menu: File->Import Data) can lead you through this easily.
For well-organized data in spreadsheets, the default settings are usually acceptable and you can simply review them and click Finish with no tweaking.
For spreadsheet data that are less organized, the level of control that the wizard offers can help a lot.
We often get questions about how the import process works within SAS Enterprise Guide.
For example, does it require ?
Does it generate a SAS program that you can reuse in batch code or in a stored process?
Here are the answers:
SAS Enterprise Guide does not require SAS/ACCESS to PC Files in order to import Microsoft Excel content.
SAS Enterprise Guide uses local Windows components to read the data from spreadsheets and then transforms them into a text-based format that SAS can import via DATA step.
(Note: the same is true when importing Microsoft Access databases, Lotus worksheets, or even .)
This means that when the step is complete you have your data in SAS, but you don't have a SAS program that represents the entire process.
SAS Enterprise Guide does some of the work behind the scenes.
That's convenient if you don't have SAS/ACCESS to PC Files on your SAS server.
It's less convenient if you are trying to build a standalone batch program.
SAS Enterprise Guide can use SAS/ACCESS to PC Files if you want.
If you do have SAS/ACCESS to PC Files on your Windows SAS server, you can check a box in the Advanced Options of the Import Data task and get SAS Enterprise Guide to generate a working program that uses PROC IMPORT with .
This is similar to how the Import Data wizard works in the SAS display manager environment.
However, there are a few requirements:
The Excel file must reside in the file system of the SAS server (your local PC if using local SAS, or the remote Windows system if your SAS workspace is on a different machine).
This is supported only when connecting to SAS on Windows.
It is possible to use
on a Unix system, but it requires a
and the , which the Import Data task does not generate for you.
If all of those planets align for you, then you can use the Import Data task to create a reusable SAS program.
Exporting SAS data in Excel format does not require or use SAS/ACCESS to PC Files.
Again, SAS Enterprise Guide uses the Windows-based APIs to create XLS files (), and PROC EXPORT is not used for this.
If you need the step to happen inside a SAS program, you can write your own PROC EXPORT step in a program node.
I hope that this clears up some of the questions and misconceptions we encounter.
But if this post doesn't do it for you, let me know in the comments.
____________博 客 平 台___________
SAS博客大汇Quick Reference for the TAGSETS.EXCELXP Tagset
This document is a quick reference to options for the TAGSETS.EXCELXP tagset.
To see this information after you , submit the following code. Also included is the current version number and a log of the changes that have been made to the tagset.
ODS tagsets.excelxp file="test.xml" options(doc="help");
Short Descriptions of the Supported Options
Default valueDescription
ASCII_DOTSyesTurn off/on leading dots in textual 'batch' output
AUTOFILTERnoneTurn on auto filter for all columns or a range of columns
AUTOFILTER_TABLE1Which table on the worksheet should get the filters
ABSOLUTE_COLUMN_WIDTHnoneList of widths to use for each column in a table no matter what
AUTOFIT_HEIGHTnoIf yes, no row heights will be specified
AUTO_SUBTOTALSnoAdd a subtotal function to the summary line of proc print
BLACKANDWHITEnoThis value turns on black and white for printing
BLANK_SHEETCreate a Blank Worksheet with the name given
CENTER_VERTICALnoThis value controls vertical centering for printing
CENTER_HORIZONTALnoThis value controls horizontal centering for printing
COLUMN_REPEATnoneRepeat columns across pages when printing
CONTENTSnoCreate a worksheet that will contain a table of contents
CONTENTS_WORKBOOKContents, IndexCreate a workbook with a table of contents and/or an index of workbooks and/or an index of worksheets
CONVERT_PERCENTAGESyesRemove percent symbol, apply Excel percent format, and multiply by 100
CURRENCY_SYMBOL$Used for detection of currency formats and for removing symbols so Excel will see currency as numbers
CURRENCY_FORMATCurrencyThe currency format specified for Excel to use
DECIMAL_SEPARATOR.The character used for the decimal point
DEFAULT_COLUMN_WIDTHnoneList of widths to use for each column in a table, if there are no widths
DOCnoneDocumentation for this tagset. Values are Help, Options, Quick, Settings, and Changelog
DPI300This value determines the dots per inch for printing
DRAFTQUALITYnoThis value turns on draft quality for printing
EMBED_TITLES_ONCEnoIf yes, embedded titles will only appear at the top of each worksheet
EMBED_FOOTERS_ONCEnoIf yes, embedded footers will only appear at the bottom of each worksheet
EMBEDDED_FOOTNOTESnoPut footnotes in the worksheet
EMBEDDED_TITLESnoPut titles in the worksheet
FITTOPAGEnoFit to Page when printing
FORMULASyesData values that start with an '=' will become formulas
FROZEN_HEADERSnoFreeze rows from scrolling with the scrollbar
FROZEN_ROWHEADERSnoFreeze columns from scrolling with the scrollbar
GRIDLINESnoThis value turns on gridlines for printing
HIDDEN_COLUMNSnonerange or list of column numbers to hide
INDEXnoCreate a worksheet that will contain a index of worksheets
MERGE_TITLES_FOOTNOTESnoMerge left justified titles and footnotes.
MINIMIZE_STYLEnoMinimize the styles written to the stylesheet. Can cause unloadable XML files
MISSING_ALIGNrSets the alignment for missing values
NUMERIC_TEST_FORMAT12.Used for determining if a value is numeric or not
ORIENTATIONPortraitPrint orientation for the worksheet, Portrait or Landscape
PAGE_ORDER_ACROSSnoIf set to yes, the worksheet page order will be set to print across, then down
PAGEBREAKSnoInsert page break lines in the worksheet
PAGES_FITWIDTH1This value determines the number of pages to fit the worksheet across when printing
PAGES_FITHEIGHT1This value determines the number of pages down to fit the worksheet when printing
PRINT_FOOTERNoneIf there are no footers, or embedded footnotes are on, this value will be used as the footer for printing
PRINT_FOOTER_MARGINnoneThis is the footer margin as set in the page setup dialog window
PRINT_HEADERNoneIf there are no titles or embedded titles are on, this value will be used as the header for printing
PRINT_HEADER_MARGINnoneThis is the header margin as set in the page setup dialog window
ROW_HEIGHT_FUDGE4A fudge value to add to the row height for each row
ROW_HEIGHTS0,0,0,0,0,0,0Positional list of point sizes to use for row heights
ROW_REPEATnoneRepeat rows across pages when printing
ROWCOLHEADINGSnoThis value turns on row and column headings for printing
SCALE100This value determines the scale level for printing
SHEET_INTERVALTableInterval to divide the output between worksheets. Values are Table, Page, Bygroup, Proc, or None
SHEET_NAMEnoneWorksheet name to use for the next worksheet
SHEET_LABELnoneReplace the prefix of the worksheet name with this value
SKIP_SPACE1,0,1,1,1Multiplier for the space that follows the different types of output
SUPPRESS_BYLINESnoSuppresses bylines in the worksheet
THOUSANDS_SEPARATOR,The character used for indicating thousands in numeric values
TITLE_FOOTNOTE_WIDTH0The number of columns titles and footnotes are allowed to span.
WIDTH_FUDGE0.75This value is used along with Width_Points and column width to calculate an approximate width for the table columns
WIDTH_POINTSnoneOverride value for width calculations
WRAPTEXTyesThis value turns wraptext on and off for all style definitions.
ZOOM100This value determines the zoom level on the worksheet
DEBUG_LEVEL0Numeric value to turn on various debug messages
CONFIGURATION_FILEnoneAn ini file to read option settings from
CONFIGURATION_NAMEnoneA section name in an ini file that holds option settings苹果/安卓/wp
/* Let's illustrate with an example.
Suppose the results we are interested in is the basic measurements of variable1 in data one*/
input variable1 variable2;
proc univariate data=
var variable1;
/* We can find the names of all results in log window:
59& &proc univariate data=
60& &var variable1;
NOTE: Writing HTML Body file: sashtml3.htm
Output Added:
-------------
Name:& && & Moments
Label:& && &Moments
Template:& &base.univariate.Moments
Path:& && & Univariate.variable1.Moments
-------------
Output Added:
-------------
Name:& && & BasicMeasures
Label:& && &Basic Measures of Location and Variability
Template:& &base.univariate.Measures
Path:& && & Univariate.variable1.BasicMeasures
-------------
Output Added:
-------------
Name:& && & TestsForLocation
Label:& && &Tests For Location
Template:& &base.univariate.Location
Path:& && & Univariate.variable1.TestsForLocation
-------------
Output Added:
-------------
Name:& && & Quantiles
Label:& && &Quantiles
Template:& &base.univariate.Quantiles
Path:& && & Univariate.variable1.Quantiles
-------------
Output Added:
-------------
Name:& && & ExtremeObs
Label:& && &Extreme Observations
Template:& &base.univariate.ExtObs
Path:& && & Univariate.variable1.ExtremeObs
-------------
NOTE: PROCEDURE UNIVARIATE used (Total process time):
& && &real time& && && &&&0.37 seconds
& && &cpu time& && && && &0.09 seconds
*Suppose we want to export the BasicMeasures to Excel. After submitting the following codes, we will find SAS data b in work library*/
ods output BasicMeasures=b;
proc univariate data=
var variable1;
1. In the previous step, we have saved the result into SAS work library and in data b.
2. Let's save SAS data b into Excel file b, and put the Excel file forb on desktop.
& &- Click on & File&on the upper left corner
& &- Click on &Export Data&
& &- Library: &WORK&, Member: &B&, and Click on &Next&
& &- Select &Microsift Excel 97, 2000 or 2002 Workbook&, and Click on &Next&
& &- Click on &Browse&
& &- Click on Desktop on the left, Filename:&forb&, and click on &Save&
& &- Click on &OK&
& &- Assign a name to the exported table: &b&, and click on &Finish&
& &- On desktop, you will see Excel file forb which contains the SAS result you have selected. */
载入中......
这个也是高难度
hongbo2009 发表于
SAS数据集导入到EXCEL中是可以的，可以直接用菜单操作，当然也可以编程实现。两种方法都介绍一下行吗？
可以先利用ods csvall输出为csv，然后自动转换成excel，比较麻烦。
ods csvall body='d:\tmp.csv';
proc means data=sashelp.
& & & &
filename dst dde'excel|system';
data _null_;
& & & & rc=system('start excel');
& & & &
data _null_;
& & & & x=sleep(5);
data _null_;
& & & &
& & & & put '[open(&d:\tmp.csv&)]';
& & & & put '[save.as(&d:\tmp.xls&,1)]';
& & & & put '[file.close()]';
& & & & put '[quit()]';
复制代码
当然可以，非常方便
还有一简便的办法,先复制到WORD里,利用WORD的文字转换成表格的功能,转换为表格后再复制到Excel中
用文本导入工具，很简单，我经常使用，大致介绍一下步骤：
1、选中所有需要存入excel的文本，复制；
2、新建excel，随便选中一个单元格，粘贴；
3、粘贴之后右下角会让你选择复制格式，选择“使用文本导入向导”
这样之后就是格式相对整齐的表格了
我是在sas9.2转入excel2007界面下操作的，别的版本没有尝试过。
huage 发表于
hehe.. SAS is very powerful..
Search option 'ods tagsets.ExcelXP'&&,
which will show how SAS output to multiple-sheet excel workbook.
Hope it does help.sas 还是有很强的功能的， 如huage所说 tagsets.excelxp 就是个不错的选择！ 好好看看吧。
不过也有缺陷，比如字符型的数值（）导入到excel 后 0 就没了，需要注意！
Need study again
又学了一招。。。
一曲肝肠断
天涯何处觅知音
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
&nbsp&nbsp|
如有投资本站或合作意向，请联系（010-）；
邮箱：service@pinggu.org
投诉或不良信息处理：（010-）
论坛法律顾问：王进律师}