| This post was kindly contributed by Ken's SAS tricks - go there to comment and to read the full post. |
Yet another useful not-a-trick.
One of the great things about SAS is the accessibility and completeness of the documentation. However, occasionally lapses in the completeness do come up.
One example is in importing data. A primary tool for this is proc import, for which the documentation is, unfortunately, terribly incomplete. (At least when using the on-line help through version 9.2.)
Fortunately, SAS provides the “Import data” wizard to be used through the GUI.
Unfortunately, repeating all those points and clicks is a hassle.
Fortunately, all it does is write a proc import for you, and you can save that in a file.
Unfortunately, you have to do that every time you need it.
Fortunately, I have it for you right here, for the important case of a Microsoft Excel file.
PROC IMPORT OUT= WORK.test DATAFILE= “C:\temp\vasc.xlsx” DBMS=EXCEL REPLACE;
RANGE=”Vascular_2005_Deciles$”;
GETNAMES=YES;
MIXED=NO;
SCANTEXT=YES;
USEDATE=YES;
SCANTIME=YES;
RUN;
For me, the biggest PIA in this is having to open the file to find the name of the sheet to type in for the range. Also note that the ‘$’ is appended to, I think, request that character columns be imported.
I hope this will be useful– I know I’ll be coming back myself to grab this every now and again.
UPDATE: I had a note from Chris Hemedinger of SAS and the SAS Dummy blog. He points to the documentation for SAS/ACCESS for details on reading Excel files with proc import. This can be found on the web at http://support.sas.com/documentation/cdl/en/acpcref/63184/HTML/default/viewer.htm#a003103761.htm, and in the on-line help via: SAS Products; SAS/ACCESS; SAS/ACCESS 9.2 for PC Files: Reference; File Format-Specific Reference; Microsoft Excel Workbook Files. This reports that the range option can be used to specify cells within each sheet; the ‘$’ alone implies that the range is the whole sheet. A similar effect can be obtained with the sheet option. I don’t know why the wizard would use the range option by default.
| This post was kindly contributed by Ken's SAS tricks - go there to comment and to read the full post. |