[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sas-l
Subject:    Cut and paste method for importing Excel data into SAS
From:       Aaron Moynahan <aaron.moynahan () VERIZON ! NET>
Date:       2003-07-31 15:13:33
[Download RAW message or body]

Here is a simple cut and past way of getting excel data into SAS that seems
to always work for me without any hassles.



Step1: Copy the range the you want import into SAS from excel into Microsoft
notepad:



Step2: Convert the invisible column delimiter into a "|" or whatever
character you may choose as your delimiter.

When you copy data from excel to notepad there is a character that is not
displayed by notepad between each cell. In the line below the first two "A'
s" are in adjacent columns in excel. To convert this invisible character
into the "|" highlight the area beteen the two "A's" and copy it. Go to edit
replace in notepad and under Find what: paste in the invisible character by
hitting shift insert. You will see that what looks some sort of vertical
line with was formerly an invisible character. Under Replace with: type in
your desired delimiter. In my sample file with around 1300 rows this took
about 30 seconds on my PC to do the search and replace.


A A Agilent Technologies, Corporation




Step 3: Create a data step in SAS with a datalines statement and just paste
the delimited data from notepad to the edit window in interactive SAS like
in the example below.



Data cboe;
  infile datalines dsd delimiter="|";
  length symbol $5 opt_sym $5 name $50 call put tot days tot_adv call_adv
put_adv 8;
  informat call put tot days tot_adv call_adv put_adv comma10.;
  input symbol $ opt_sym $ name $ call put tot days tot_adv call_adv
put_adv;
datalines;
A|A|Agilent Technologies, Corporation|7,514|1,935|9,449|21|450|358|92
AA|AA|ALCOA Inc.|12,631|4,123|16,754|21|798|601|196
AAI|AAI|Airtran Holdings, Inc|981|330|1,311|21|62|47|16
;
run;



As ridiculous as this method may appear it seems to always work without any
errors or hassles.

[prev in list] [next in list] [prev in thread] [next in thread]