DATA Step, Macro, Functions and more

how to append two observations based on a condition

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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

 

 

Please help if possible.

Thanks in advance.


Accepted Solutions
Solution
‎01-17-2018 06:35 PM
Trusted Advisor
Posts: 1,294

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;

View solution in original post


All Replies
PROC Star
Posts: 1,357

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;
PROC Star
Posts: 1,357

Re: how to append two observations based on a condition

Posted in reply to novinosrin
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

Re: how to append two observations based on a condition

Posted in reply to novinosrin

Hi 

 

 

PROC Star
Posts: 1,357

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,543

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

Posted in reply to Astounding

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,543

Re: how to append two observations based on a condition

@rajdeep,

 

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
Trusted Advisor
Posts: 1,294

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

Re: how to append two observations based on a condition

Hi 

 

 

 

 

 

Trusted Advisor
Posts: 1,294

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 191 views
  • 0 likes
  • 4 in conversation