topic Re: PROC APPEND not updating Excel file in SAS Programming

PROC APPEND not updating Excel file

arunrami — Tue, 08 Jan 2019 11:01:12 GMT

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.

Re: PROC APPEND not updating Excel file

Kurt_Bremser — Tue, 08 Jan 2019 11:21:33 GMT

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.

Re: PROC APPEND not updating Excel file

arunrami — Tue, 08 Jan 2019 12:53:35 GMT

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??

Re: PROC APPEND not updating Excel file

cosmid — Tue, 08 Jan 2019 14:21:27 GMT

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.

Re: PROC APPEND not updating Excel file

Kurt_Bremser — Tue, 08 Jan 2019 14:48:45 GMT

@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).

Re: PROC APPEND not updating Excel file

arunrami — Tue, 08 Jan 2019 15:03:01 GMT

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 🙂