Adding a single record in SAS?

Question

I've found how to append a table to a base with proc append. I'm wondering if there is a simpler way (that doesn't require creating a temporary holding table) that adds a single record? All solutions I find require several lines, I'm wondering if there's something more straightforward like the "delete" command that I'm missing?

Answer 1

You can use a data step with a modify statement to insert additional rows. Any other sort of data step involves creating a temporary file and replacing the original, which is undesirable when you're making a small change to a large dataset.

data class;
    set sashelp.class;
run;

data class;
    modify class;
    Name = 'ZZZ';
    output;
    stop;
run;

This reads in the first row of the dataset to the PDV and then appends it with an updated value of Name, leaving the values of all the other variables as they were in the first row.

N.B. without the stop statement, this will cause an infinite loop, as SAS will alternately add and append records indefinitely, never getting any closer to the end of the dataset.

Answer 2

If you want to conditionally add observations from another dataset then you can use output to tell SAS to output the PDV as it stands:

Generate example input datasets:

data source1 ; 
  do i=1 to 10 ;
    output ;
  end ;
run; 

data source2 ;
  i=999 ;
  do j=100 to 0 by -10 ;
    output ;
  end ;
run ;

Code to add in single record:

Read in source1 and output the observation before testing the condition (i=5) to read in an observation from source2:

data want ;
  set source1 ;
  output ;
  if i=5 then do ;
    set source2 ;
    output ;
  end ;
run; 

However, it's important to know the interaction between the two set statements:

• Any variables that are in BOTH datasets will be overwritten by either set statement - so the value of this variable will be updated even if the previous value has not been output (see observation number 6 of the want dataset)

• Any variables that are only in ONE dataset will effectively be 'retained' across observations until another set statement for the appropriate source dataset is encountered (see observations 7 to 11 of the want dataset)

For details of the PDV processing logic see here

Answer 3

As I think you have found, there are multiple options. If you have values you want to add, the one that makes the most sense from a lay person reading your code is a PROC SQL INSERT statement.

data test;
format a best. b $8. c date9.;
a = 1;
b = "ABC";
c = "01JAN2015"d;
run;

proc sql;
insert into test (b,c,a) 
values ("DEF", "01JAN2014"d, 2);
quit;

The specification of the variable order (b,c,a) is optional. If it is not specified, then the values must be in the same order as the columns on the table.