How To Append Data from Excel to SAS

[Cap2010]

hi,

Want to update / append SAS data from XL
below is the sas command and it gives error
Unable to append data in SAS, secondly do I have to sort too after appending data


Fields
Tname is text
Counter is number
Sdate is date
TTime is time


option notes source;
FILENAME book1 DDE "excel|[book1.xls]sheet1!r2c1:r14c4";
DATA dat.test append;
INFILE book1 dlm=",";
informat tname $12. Counter $12. SDate TTime;
INPUT
RUN;
quit;


NOTE: Invalid data for tname in line 1 1-1.
RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-
1 A 2 3
tname=. Countt=2 _ERROR_=1 _N_=1
NOTE: Invalid data for tname in line 2 1-1.
2 B 2 3

 

[klaz2002]

Do you have a semi-colon ';' after the INPUT statement? Speaking of that statement try INPUT var_name $ var_name2 $..; (the dollar sign is for character variables.)

Hope that this helps you.
Klaz

 

[Cap2010]

Klaz,

Thanks, it works with $ sign for character
and have given ; end of INPUT command.

Is there any other sign for numbers, date and datetime ?
Above SAS overwrites with new records, want to append records to the existing record table.

Cap2010

 

[klaz2002]

If you would like to add to a dataset you can use the proc append after you read in the data. You may also use the SET statement in a datastep.

ex1.
FILENAME book1 DDE "excel|[book1.xls]sheet1!r2c1:r14c4";
DATA your_temp_ds_name;
INFILE book1 dlm=",";
informat tname $12. Counter $12. SDate TTime;
INPUT $;
RUN;
proc append base=final data=your_temp_ds_name;
run;

ex2
FILENAME book1 DDE "excel|[book1.xls]sheet1!r2c1:r14c4";
DATA your_temp_ds_name;
INFILE book1 dlm=",";
informat tname $12. Counter $12. SDate TTime;
INPUT $;
RUN;
data final;
set your_temp_ds_name
other_ds
etc
etc2;
*** you get the picture;
run;
proc print data=final
run;

Klaz

 

[Cap2010]

Klaz,

it works, below is the code

FILENAME book1 DDE "excel|[book1.xls]sheet1!r2c1:r14c4";
DATA work.test;
INFILE book1;
informat name $12. Counter $12.;
INPUT tname $ Counter;
RUN;
proc append base=dat.test data=work.test;
run;
quit;

Cap2010