Create a new row using another row

[serena13lee]

I have a data set that looks like the following: 

Column 1  Column 2   Column 3
Data                4                3 
Data 1             3                2 
Data 2             3                4

I would like to insert a row under the title names that duplicates  row Data 2 renamed. 

Column 1  Column 2   Column 3
New                3                4
Data                4                3 
Data 1             3                2 
Data 2             3                4

How can I do so? I've seen this used:

proc sql;
create table totals as

 but I'm just looking for the easiest way. 

[novinosrin · Accepted solution]

Assuming i Understand your requirement-->

data have;
input (Column1- Column3) (& $8.);
cards;
Data                4                3 
Data 1             3                2 
Data 2             3                4
;

data want;
if _n_=1 then do;
set have(where=(column1='Data 2'));
Column1='New';
output;
end;
set have;
output;
run;

[novinosrin]

data have;
input (Column1- Column3) (& $8.);
cards;
Data                4                3 
Data 1             3                2 
Data 2             3                4
;

proc sql;
create table want as
select 'New' as Column1,Column2,Column3 from have(where=(column1='Data 2'))
union all
select * from have;
quit;

[novinosrin]

More fun

data have;
input (Column1- Column3) (& $8.);
cards;
Data                4                3 
Data 1             3                2 
Data 2             3                4
;
data want;
set have(where=(column1='Data 2'))  have;
if _n_=1 then Column1='New';
run;

[mkeintz]

In essence, you want to concatenate two data sets:

1. A single observation data set comprised of observation 23 of have
2. Followed by all of have:

This is a good situation to take advantage of the fact that the SET statement can take multiple data sources.  All you have to do is define those sources to produce #1 and #2 above:

data have;
input (Column1- Column3) (& $8.);
cards;
Data                4                3 
Data 1             3                2 
Data 2             3                4
;

data want;
  set have (firstobs=3 obs=3)  have;
  if _n_=1 the columns1='New';
run;

Code above edited from ("firstobs=2 obs=2") to ("firstobs=3 obs=3") per @novinosrin's acute note.

The first argument of SET tells sas to read from data set HAVE, starting at observation 23 and ending at observation 23.  The second argument says to read all of HAVE, from the 1st through last observation.

The "if _n_=1" test makes a change only for the first iteration of the data step - i.e. the 1st incoming observation.  Note it does NOT mean the first observation from each of the concatenated data sources.

Now, if the criterion for the single prefix record is, as @novinosrin suggested, then change the SET statement to the below:

data want;
  set have (where=(column1='Data 2') obs=1)  have;
  if _n_=1 the columns1='New';
run;

This tells SAS to read the first (and only the first) observation that has column1='Data 2'.  Note the "obs=1" only applies to the observations that pass the WHERE filter.  So observations 1 (column1='Data') and 2 (column1='Data 1') are not counted.

[novinosrin]

@mkeintz  is this a typo

 set have (firstobs=2 obs=2)  have;

Shouldn't it be 

 set have (firstobs=3 obs=3)  have;

 ?

Btw, I like the

 set have (where=(column1='Data 2') obs=1)  have;

Very neat & efficient. Kudos!