topic Re: Append Leaves Numeric Columns Blank in SAS Programming

Append Leaves Numeric Columns Blank

shl007 — Tue, 08 Aug 2023 21:00:36 GMT

I have a proc append issuing the following notes. I assume none of the notes below would explain why a numeric column would be empty (when the append dataset has it populated)?

What is happening is, the dataset containing the rows to be appended has several numeric columns populated, but when the append executes, those numeric columns in the base dataset are left blank. No warnings or other messages are in the log from the append other than the notes below.

Any ideas on why the numeric columns are blank after the append? Does the large dataset size have something to do with SAS not being able to append? One thought a colleague had was to make the columns character to see if that allows the columns to be populated after the append.

INFO: Engine's fast-append process in use.
INFO: 03:26:04 Reading DATA file, updating BASE file
INFO: Starting data set size is 1005557 pages
INFO: Engine's block-read method cannot be used because:
INFO: - The file is compressed
INFO: Ending data set size is 1007791 pages, 2234 added.
INFO: 00:00:00 Elapsed time
INFO: 03:26:04 Finished updating BASE file

Re: Append Leaves Numeric Columns Blank

ballardw — Tue, 08 Aug 2023 21:21:28 GMT

Show the ENTIRE log, the code and all messages.

Are you appending to a SAS data set or an external database file? External files, i.e. not native SAS data sets can sometimes be problematic. If an external data base has a constraint on a field such that it will only accept certain numeric values I might expect them to be "blank" if you attempt to append values that are not accepted.

Format associated with the columns in the base file?

Attempting to "append" character values to a numeric field with proc append should result in errors as the procedure doesn't append incorrect data types.

Re: Append Leaves Numeric Columns Blank

Quentin — Tue, 08 Aug 2023 22:12:45 GMT

Can you post the full log, showing the PROC APPEND code you ran and all the Notes and other messages after that? There's nothing in those INFO: messages that would explain your problem. But PROC APPEND is pretty good about sending messages to the log when it encounters problems.

Re: Append Leaves Numeric Columns Blank

Tom — Tue, 08 Aug 2023 22:27:19 GMT

What you describe sounds like what would happen if the variables did not exist in the source dataset.

But you should see a message about that.

754  data class;
755   set sashelp.class;
756   where sex='M';
757  run;

NOTE: There were 10 observations read from the data set SASHELP.CLASS.
      WHERE sex='M';
NOTE: The data set WORK.CLASS has 10 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.04 seconds


758
759  proc append
760    base=class
761    data=sashelp.class(where=(sex='F') drop=age)
762  ;
763  run;

NOTE: Appending SASHELP.CLASS to WORK.CLASS.
WARNING: Variable Age was not found on DATA file.
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
      WHERE sex='F';
NOTE: 9 observations added.
NOTE: The data set WORK.CLASS has 19 observations and 5 variables.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds