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.
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;
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;
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;
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;
In essence, you want to concatenate two data sets:
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.
@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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.