Easy Data Movement between SAS and Microsoft Excel - Exchanging Data Between SAS and Microsoft Excel - page 7

Figure 1.3.4: Excel Worksheet after Converting the Text in Column A to Columns of Data.

1.3.3 Copy Data to the SAS Enhanced Editor Window for Use in a SAS
Program
This example highlights and copies the data in Figure 1.3.4 cells C3 to I8 into the SAS code segment
below. When data is copied from Excel, tab characters are used to separate the data columns. This code
shows the use of the INFILE command with the DLM option to identify the SPACE character as the
delimiter within the SAS code. The irregular spacing shown on the lines following the “CARDS;”
statement is caused by the character fields not being the same length and the characters shifting columns of
data. The TRUNCOVER option causes the data to read correctly.

DATA Copy_n_paste;

INFILE DATALINES DLM = ' ' TRUNCOVER;
INPUT Region $ Product $ Subsidiary $ Stores Sales $ Inventory $
Returns $;
CARDS;
Asia Boot Bangkok 1 $1,996 $9,576 $80

Asia Sandal Bangkok 1 $3,230 $15,087 $120

Asia Slipper Bangkok 1 $3,019 $16,075 $127

Asia Boot Seoul 17 $60,712 $160,589 $1,296

Asia Sandal Seoul 3 $4,978 $21,483 $105

Asia Slipper Seoul 21 $149,013 $469,007 $2,941

;
RUN;

Running the sample SAS code that appears above Figure 1.3.1 will produce the SAS dataset with six
observations and seven variables shown below in Figure 1.3.5.