topic Re: Insert Nth row in existing dataset in SAS Programming

Insert Nth row in existing dataset

BrahmanandaRao — Fri, 11 Mar 2022 09:18:27 GMT

Here I want to insert row customize for below example i want output all rows along with new insert observation

data custom_insert;
set sashelp.class;
run;


data row;
set custom_insert;
if _n_=3 then do 
name='Sachin';
sex= 'f';
age=22;
height=12;
weight=35;
output;
end;
run;

Re: Insert Nth row in existing dataset

Patrick — Fri, 11 Mar 2022 09:49:53 GMT

If I understand the question then a 2nd OUTPUT right after the SET statement should do the job.

data row;
  set custom_insert;
  output;

  if _n_=3 then
    do;
      name='Sachin';
      sex= 'f';
      age=22;
      height=12;
      weight=35;
      output;
    end;
run;

Re: Insert Nth row in existing dataset

BrahmanandaRao — Fri, 11 Mar 2022 10:00:26 GMT

Thank You Patick

If we insert very first row the dataset how can we achieve i want Sachin would be first record

Re: Insert Nth row in existing dataset

sbxkoenk — Fri, 11 Mar 2022 10:36:00 GMT

Hello,

Do you want this?

data custom_insert;
set sashelp.class;
run;

data row;
set custom_insert;
 if _n_=1 then do;
  output;
  name='Sachin';
  sex= 'f';
  age=22;
  height=12;
  weight=35;
end;
output;
run;

Koen

Re: Insert Nth row in existing dataset

data_null__ — Fri, 11 Mar 2022 14:05:06 GMT

data custom_insert;
   set sashelp.class;
   run;

data newrow;
   if 0 then set custom_insert;
   name='Sachin';
   sex= 'f';
   age=22;
   height=12;
   weight=35;
   output;
   stop;
   run;

data row;
   set newrow custom_insert;
   run;

Re: Insert Nth row in existing dataset

mkeintz — Fri, 11 Mar 2022 14:14:50 GMT

Here is a slight generalization of what you've been offered:

%let new_row=23;

data want;
  if 0 then set sashelp.class  nobs=nrecs;
  if _n_=min(&new_row,nrecs+1) then do;
    name='Sachin';
    sex= 'f';
    age=22;
    height=12;
    weight=35;
    output;
  end;
  set sashelp.class;
  output;
run;

The "if 0 then set ..." statement is just to have SAS establish the variable attributes. This avoids an unintentional attribute established in the DO group.

The "if _n_= " expression test for desired row number. If the desired row number exceeds the number of incoming observations, then the new record is appended at the end of the dataset.

Re: Insert Nth row in existing dataset

Patrick — Sat, 12 Mar 2022 11:24:47 GMT

The row where you insert a record shouldn't matter. If it where a database table then the concept of a row number wouldn't even exist. This is "Excel thinking" which you better get rid-off when working with SAS or any database.

But with SAS: Below one option.

data row;
  if 0 then set sashelp.class;
  if _n_=1 then
    do;
      name='Sachin';
      sex= 'f';
      age=22;
      height=12;
      weight=35;
      output;
    end;
  set sashelp.class;
  output;
run;

Re: Insert Nth row in existing dataset

mkeintz — Sat, 12 Mar 2022 15:37:33 GMT

@Patrick wrote:

The row where you insert a record shouldn't matter. If it where a database table then the concept of a row number wouldn't even exist. This is "Excel thinking" which you better get rid-off when working with SAS or any database.

I beg to differ.

Any DATA step or PROC step that uses a BY statement almost always is much more efficient if the "rows" are sorted (as opposed to indexed) by the BY variables. Historical data, time series, any programming that depends on use of the LAG function, FIRSTOBS or OBS parameters, or POINT= options will depend on users being aware of row locations.

And being conscious of data as sequential observations predates "Excel thinking" by decades.