Solved
Contributor
Posts: 26

# how to append two observations based on a condition

Hi Folks,

I am trying to append two rows one after another based on a condition

``````
data test1;

input x \$1-15;
cards;
Hello There
Oh! No
Hello World
See You Guys
Oh! Yes
;
run;

data test2;
set test1;
if findw(cat,'DATA',1,'.','i')  then
do
Concat_var1=_n_;

end;
Concat_var2=Concat_var1-1;
if scan(cat,1)='DATA'  and Concat_var1 eq . then Concat_var1=Concat_var2;
run;``````

Expected Output :

Whenever the "Oh!" word encounters it should be appended with just to prior row value. Like for example in the output should be like below

``````
Hello There Oh! No
Hello World
See You Guys Oh! Yes``````

Accepted Solutions
Solution
‎01-17-2018 06:35 PM
Posts: 1,392

## Re: how to append two observations based on a condition

``````data have;
input text \$1-21;
cards;
Hello There
Oh! No
Hello World
See You Guys
Oh! Yes
run;
data want;
merge have
have (firstobs=2 rename=(text=text_next));
if text_next =: 'Oh!' then text=catx(' ',text,text_next);
if not(text=:'Oh!');
drop text_next;
run;``````

All Replies
Super User
Posts: 2,061

## Re: how to append two observations based on a condition

`````` data test1;

input x \$1-15;
cards;
Hello There
Oh! No
Hello World
See You Guys
Oh! Yes
;
run;

data want;
set test1;
length t \$50;
k=lag(x);
if find(x,'Oh!')>0 then do; t=catx(' ',k,x);output;end;
drop k;
run;``````
Super User
Posts: 2,061

## Re: how to append two observations based on a condition

``````data want;
set test1;
length t \$50;
k=lag(x);
if find(x,'Oh!')>0 then do; t=catx(' ',k,x);output;end;
else if find(k,'Oh!')>0 then do; t=x; output;end;
drop k;
run;``````
Contributor
Posts: 26

Hi

Super User
Posts: 2,061

## Re: how to append two observations based on a condition

Yes, I realized that. So, posted another one. Did you test the most recent post of mine?

Super User
Posts: 6,933

## Re: how to append two observations based on a condition

Because of the need to output when Oh is not present on the next line ...

data want;

length x \$ 30;

merge test1 test1 (firstobs=2 rename=(x=y));

if index(x, 'Oh!') then delete;

if index(y, 'Oh!') then x = catx(' ', x, y);

drop y;

run;

I can't test it right now, but it looks about right.

Contributor
Posts: 26

## Re: how to append two observations based on a condition

Hi Astounding,

Thanks for the reply. The firstobs=2 is actually limiting the data. Like in the real scenario, suppose the "Oh!" word is not starting at row no.2 and think if there would be  more than 1000 of records then we might not open the dataset each time to look at, in order to know about the "Oh!" word starting row.

Looking for a generic solution.

Thanks

Super User
Posts: 6,933

## Re: how to append two observations based on a condition

Having had time to test this, it works just fine.  FIRSTOBS=2 just sets the starting point ... which observation is the first one to bring in.  But SAS brings in the next one, then the next one, etc.  It should be easy enough for you to test the code on a small number of observations to be sure.

As illustrated  in the @mkeintz post, it makes a difference whether you are looking for "Oh!" at the beginning of the next line, or "Oh!" anywhere within the next line.  My solution looks for "Oh!" anywhere within the next line.

Solution
‎01-17-2018 06:35 PM
Posts: 1,392

## Re: how to append two observations based on a condition

``````data have;
input text \$1-21;
cards;
Hello There
Oh! No
Hello World
See You Guys
Oh! Yes
run;
data want;
merge have
have (firstobs=2 rename=(text=text_next));
if text_next =: 'Oh!' then text=catx(' ',text,text_next);
if not(text=:'Oh!');
drop text_next;
run;``````
Contributor
Posts: 26

Hi

Posts: 1,392

## Re: how to append two observations based on a condition

@rajdeep

What do you mean by "If I have more than 1000 records then it might not work."?   I presume you tested the program I provided.  It worked for 5 records.  Why would it fail with 1,000?

Contributor
Posts: 26

## Re: how to append two observations based on a condition

Hi

☑ This topic is solved.