Copy and Paste from Excel to SAS®

Next Presentation:

Presenter: Arthur Tabachneck

Copy and Paste from Word or Excel to SAS

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

Arthur Tabachneck

Thornhill, ON Canada

Matt Kastin

Penn Valley, PA

Suppose you have data in an Excel workbook

or a table in a Word Document

and you had a one-time need

to import the data into a SAS dataset

you have SAS/Access interface to PC files

you don't have a 32/64 bit compatibility issue

and

then you could do it using PROC IMPORT:

proc import out= work.want

datafile= "c:\orsales.xlsx"

dbms=xlsx replace;

run;

if the data are in an Excel workbook and

your SAS version supports Excel 2010

and

your SAS version doesn't support Excel 2010

you don't have SAS/Access Interface to PC files

but, what if:

you keep running into 32/64 bit compatibility

issues

and/or

you need more control over formats, informats

or variable names

and/or

the table is in something other than Excel (e.g.,

Microsoft Word or a web page)

and/or

a simple solution?

a datastep that uses the clipboard access method

gives you total control over all formats and informats

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

but the Clipboard Access Method will work as follows:

How it works

Open the workbook in Excel

Press Ctrl-A, then press Ctrl-C

selects all of the workbook's cells

copies the selected cells to your

computer's clipboard

then

Run a datastep in SAS

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;

Open the Word file

Click on the table move handle (i.e., )

Press Ctrl-C

Works for tables copied from either Word or Excel

selects all of the table's cells

copies the selected cells to your

computer's clipboard

Works for tables copied from either Word or Excel

then just run the same SAS code

won't work if any record contains more than 256 characters

Works for tables copied from either Word or Excel

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;

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;

A method that works with both Word and Excel, on all

systems, and for records with more than 256 characters

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;

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:

Summary

with both Excel and Word

with record lengths greater than 256 characters

without being operating system specific

with various combinations of missing data

Your comments and questions

are valued and encouraged

Contact the Authors

Arthur Tabachneck, Ph.D.

Vice President, The Catalog

Thornhill, ON

[email protected]

Matt Kastin

I-Behavior, Inc.

Penn Valley, PA

matthew.kastin@gmail.com