topic Create a new row using another row in SAS Programming

Create a new row using another row

serena13lee — Thu, 20 Dec 2018 17:06:24 GMT

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.

Re: Create a new row using another row

novinosrin — Thu, 20 Dec 2018 17:11:42 GMT

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;

Re: Create a new row using another row

novinosrin — Thu, 20 Dec 2018 17:18:13 GMT

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;

Re: Create a new row using another row

novinosrin — Thu, 20 Dec 2018 17:29:35 GMT

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;

Re: Create a new row using another row

mkeintz — Thu, 20 Dec 2018 19:02:04 GMT

In essence, you want to concatenate two data sets:

A single observation data set comprised of observation 23 of have
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.

Re: Create a new row using another row

novinosrin — Thu, 20 Dec 2018 17:42:52 GMT

@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!