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
Please help if possible.
Thanks in advance.
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;
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;
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;
Hi novinosrin,
Thanks for the quick reply, but it's deleting the "Hello world" line from the output. That is also needed.
Thanks in advance.
Yes, I realized that. So, posted another one. Did you test the most recent post of mine?
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.
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
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.
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;
Hi mkeintz,
I am looking for a generic solution. The firstobs=2 is actually limiting the data making it limited to the scenario specific. If I have more than 1000 records then it might not work.
Hope you can understand.
Thanks
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?
Hi mkeintz,
Thanks for your help. I am sorry I was trying something wrong. Now it's working. Thanks a lot for your help.
Cheers.
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.