1 / 19

Next Presentation:

Next Presentation:. Copy and Paste from Word or Excel to SAS. Presenter: Arthur Tabachneck.

bailey
Download Presentation

Next Presentation:

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Next Presentation: Copy and Paste from Word or Excel to SAS Presenter:Arthur Tabachneck Art holds a PhD from Michigan State University, has been a SAS user since 1974, is president of the Toronto Area SAS Society and has received such recognitions as the SAS Customer Value Award (2009), SAS-L Hall of Fame (2011), SAS Circle of Excellence (2012) and, in 2013, was recognized as being the first SAS Discussion Forum participant to be awarded more than 10,000 points

  2. Copy and Paste from Word or Excel to SAS Arthur Tabachneck Thornhill, ON Canada Matt Kastin Penn Valley, PA

  3. Suppose you have data in an Excel workbook

  4. or a table in a Word Document

  5. and you had a one-time need to import the data into a SAS dataset

  6. if the data are in an Excel workbook and your SAS version supports Excel 2010 and you have SAS/Access interface to PC files and you don't have a 32/64 bit compatibility issue then you could do it using PROC IMPORT: proc import out= work.want datafile= "c:\orsales.xlsx" dbms=xlsx replace; run;

  7. but, what if: the table is in something other than Excel (e.g., Microsoft Word or a web page) and/or your SAS version doesn't support Excel 2010 and/or you don't have SAS/Access Interface to PC files and/or you need more control over formats, informats or variable names and/or you keep running into 32/64 bit compatibility issues

  8. a simple solution? a datastep that uses the clipboard access method gives you total control over all formats and informats

  9. if only if it worked that way, but .. you can't include notab and lrecl options with the method won't work if any records have more than 256 characters will fail if there are any missing cells in the data

  10. but the Clipboard Access Method will work as follows:

  11. How it works Open the workbook in Excel 1 Press Ctrl-A, then press Ctrl-C 2 selects all of the workbook's cells copies the selected cells to your computer's clipboard

  12. then Run a datastep in SAS 3 filename clippy clipbrd; data orsales_short; attrib Quarter label='Quarter' length=8 informat = yyq6. format=yyq6. Product_Group label='Group' length=$ 25 informat= $25. format=$25. Quantity label='Number of Items' length=8 informat=6. format= 6. Profit label='Profit in USD' length=8 informat=12. format=12.2 Field163 label='Short' length=$ 163 informat=$163. format= $163.; infile clippy missover firstobs=2; input; _infile_ = transtrn( trim( _infile_ ) , ' ' , '09'x ); quarter = input( scan( _infile_ , 1 , '09'x ,'m') , yyq6. ) ; product_group = scan( _infile_ , 2 , '09'x ,'m') ; quantity = input( scan( _infile_ , 3 , '09'x ,'m') , 6. ) ; profit = input( scan( _infile_ , 4 , '09'x ,'m') , 12. ) ; field163 = scan( _infile_ , 5 , '09'x ,'m') ; run; filename clippy clear;

  13. Works for tables copied from either Word or Excel Open the Word file 1 Click on the table move handle (i.e., ) 2 Press Ctrl-C 3 copies the selected cells to your computer's clipboard selects all of the table's cells

  14. Works for tables copied from either Word or Excel then just run the same SAS code

  15. Works for tables copied from either Word or Excel won't work if any record contains more than 256 characters one alternative for longer records, but only for tables copied from Excel and only on systems that can use DDE: filename clippy dde 'clipboard'; data orsales; infile clippy lrecl=400 dsd notab missover dlm='09'x firstobs=2; informat long_field $char327.; informat quarter yyq6.; format quarter YYQ6.; informat product_group $char24.; input quarter product_group quantity profit long_field; run; filename clippy clear;

  16. A method that works with both Word and Excel, on all systems, and for records with more than 256 characters data want; attrib _inline_ length = $ 32767 Quarter label = 'Quarter' length = 8 informat = yyq6. format = yyq6. Product_Group label = 'Product Group' length = $ 24 informat = $24. format = $24. Quantity label = 'Number of Items' length = 8 informat = 6. format = 6. Profit label = 'Profit in USD' length = 8 informat = 12. format = 12.2 field163 label = 'Short Field' length = $ 163 informat = $163. format = $163. ; keep quarter product_group quantity profit field163; rc = filename( 'clippy' , ' ' , 'clipbrd' ); if ( rc ne 0 ) then link err; fid = fopen( 'clippy' , 's' , 32767 , 'V' ); if ( fid eq 0 ) then link err; do _n_=1 by 1 while( fread( fid ) = 0 ); rc = fget( fid , _inline_ , 32767 ); _inline_ = transtrn( trim( _inline_ ) , ' ' , '09'x ); if _n_=1 then continue; quarter = input( scan( _inline_ , 1 , '09'x ) , yyq6.,'m') ; product_group = scan( _inline_ , 2 , '09'x,'m' ) ; quantity= input( scan( _inline_ , 3 , '09'x ) , 6.,'m' ) ; profit = input( scan( _inline_ , 4 , '09'x ) , 12.,'m' ) ; field163= scan( _inline_ , 5 , '09'x,'m' ) ; output; end; rc = fclose( fid ); rc = filename( 'clippy' ); stop; err: do; m = sysmsg(); put m; stop; end; run;

  17. Summary three methods were presented for copying and pasting from Excel workbooks and Word tables only one method, the Clipboard Access Method using functions, worked in all cases: with both Excel and Word without being operating system specific with various combinations of missing data with record lengths greater than 256 characters

  18. Questions?

  19. Your comments and questions are valued and encouraged Contact the Authors Arthur Tabachneck, Ph.D. Vice President, The Catalog Thornhill, ON art297@rogers.com Matt Kastin I-Behavior, Inc. Penn Valley, PA matthew.kastin@gmail.com

More Related