Getting an error when attempting to append data to an existing Excel tab

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Getting an error when attempting to append data to an existing Excel tab

I have a spreadsheet created with multiple tabs, that I export and append data to.  I use a LIBNAME statement to access the spreadsheet through SAS, and then proc export and proc append statements to update the tabs.  After running the proc append statement the first time, the appended rows are not visible in the SAS table (but do show on the spreadsheet).  I'm then not able to append additional records to the Excel tab the next time I run the code.  The export statements work without error.  I've pasted the LIBNAME and append statements below.  Any assistance or suggestions would be appreciated.

 

LIBNAME cancel EXCEL '\\atc1sasapp01\data\eric\Cancellation Data\Metrics\Cancellation Metrics.xlsx'
SCAN_TEXT=yes;

 

proc append base=cancel.future_flagged data=future_flagged4 force;
run;

 

NOTE: Appending WORK.FUTURE_FLAGGED4 to CANCEL.future_flagged.
WARNING: Variable 'Creation Short Date'n has different lengths on BASE and DATA files (BASE 255 DATA 6).
WARNING: Variable bucket has different lengths on BASE and DATA files (BASE 255 DATA 11).
NOTE: There were 1 observations read from the data set WORK.FUTURE_FLAGGED4.
NOTE: 0 observations added.
NOTE: The data set CANCEL.future_flagged has . observations and 5 variables.
ERROR: Execute: Unable to insert row
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 0.01 seconds
cpu time 0.00 seconds

NOTE: The SAS System stopped processing this step because of errors.


Accepted Solutions
Solution
‎10-01-2015 04:54 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Getting an error when attempting to append data to an existing Excel tab

Why would you want to do that in the first place, there are numerous better options:

1) Don't use Excel in the first place.

2) Import the Excel, do any updating, then export data back.

3) Export your SAS data to CSV, then have your Excel sheet load the additional data (using VBA)

 

Judging by your log problems what is happening is this.  When you create the libname, SAS applies what you can thin of as an import.  Thus it becomes a dataset with formats/lengths etc. as per a dataset.  These don't appear to match the dataset you are trying to append, so it fails.  If you look at the column properties you will see differrences.  Again, just use a decent report format (which does not include Excel).

View solution in original post


All Replies
Solution
‎10-01-2015 04:54 PM
Esteemed Advisor
Esteemed Advisor
Posts: 7,203

Re: Getting an error when attempting to append data to an existing Excel tab

Why would you want to do that in the first place, there are numerous better options:

1) Don't use Excel in the first place.

2) Import the Excel, do any updating, then export data back.

3) Export your SAS data to CSV, then have your Excel sheet load the additional data (using VBA)

 

Judging by your log problems what is happening is this.  When you create the libname, SAS applies what you can thin of as an import.  Thus it becomes a dataset with formats/lengths etc. as per a dataset.  These don't appear to match the dataset you are trying to append, so it fails.  If you look at the column properties you will see differrences.  Again, just use a decent report format (which does not include Excel).

Occasional Contributor
Posts: 6

Re: Getting an error when attempting to append data to an existing Excel tab

Thank you for the feedback.  I have to use Excel, due to end user requirements.  Importing the data, appending, and exporting the complete dataset is something I hadn't considered, and will probably solve my problem.  I appreciate your help!

Grand Advisor
Posts: 10,210

Re: Getting an error when attempting to append data to an existing Excel tab

If you are importing more than one spreadsheet you may still have issues with append depending on how you bring data into SAS as variable types may change from sheet to sheet and append doesn't like that. Also the length of character variables will be limited to the length of the first or "base" data set. But if it is only one in and one out these issues shouldn't survace.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 299 views
  • 0 likes
  • 3 in conversation