PROC APPEND not updating Excel file

[arunrami]

Hello Friends,

I am trying to update the existing excel sheet using using proc append( Need to add extra row) but I am getting following error message. Can anyone help what is wrong?

51         proc append base= test.sheet1 data=dummy force;
ERROR: Update access is not supported for file TEST.sheet1.DATA.
52         run;

p.s - I am accesing the excel file using lib name statement.

[Kurt_Bremser · Accepted solution]

Pull the existing sheet to a SAS dataset, do the append in SAS, and then replace the sheet in the Excel file. As the message says, you can't append via libname excel.

[arunrami]

Thanks Brem! One more sub question- I hope you don'e mind answering it.. While using proc append , its always taking the length of the variable for from the base data set and newly added variable are truncated accordingly.. is there any way to make it flexible or redefining the length based on new data??

[cosmid]

I don't think so. With Proc Append, the base file prevails in everything. The only way is to modify the attributes in the base file before running Proc Append.

[Kurt_Bremser]

---

@arunrami wrote:

Thanks Brem! One more sub question- I hope you don'e mind answering it.. While using proc append , its always taking the length of the variable for from the base data set and newly added variable are truncated accordingly.. is there any way to make it flexible or redefining the length based on new data??

---

No. proc append does exactly what its name says, and since it does not touch the existing data in the dataset (and therefore can't change that), the attributes of the base will prevail.

To change the attributes of the existing data, the dataset needs to be rewritten (rename of variables or a change of formats can be done with proc datasets without a complete rewrite, but length changes need a rewrite).

[arunrami]

Thanks a lot!! you are a real expert in SAS
My kind request - Please share your if are already having blog or youtube for SAS teaching,, if not please start something like that .. I will be the first to follow 🙂