topic Re: how to append two observations based on a condition in SAS Programming

how to append two observations based on a condition

rajdeep — Tue, 16 Jan 2018 22:33:07 GMT

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.

Re: how to append two observations based on a condition

novinosrin — Tue, 16 Jan 2018 22:40:51 GMT

 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;

Re: how to append two observations based on a condition

novinosrin — Tue, 16 Jan 2018 22:46:54 GMT

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;

Re: how to append two observations based on a condition

rajdeep — Tue, 16 Jan 2018 23:31:03 GMT

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.

Re: how to append two observations based on a condition

novinosrin — Tue, 16 Jan 2018 23:44:41 GMT

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

Re: how to append two observations based on a condition

Astounding — Tue, 16 Jan 2018 23:48:17 GMT

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.

Re: how to append two observations based on a condition

mkeintz — Wed, 17 Jan 2018 01:24:51 GMT

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;

Re: how to append two observations based on a condition

rajdeep — Wed, 17 Jan 2018 05:19:40 GMT

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

Re: how to append two observations based on a condition

rajdeep — Wed, 17 Jan 2018 05:28:02 GMT

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

Re: how to append two observations based on a condition

Astounding — Wed, 17 Jan 2018 14:37:19 GMT

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

Re: how to append two observations based on a condition

mkeintz — Wed, 17 Jan 2018 19:53:13 GMT

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

Re: how to append two observations based on a condition

rajdeep — Wed, 17 Jan 2018 23:37:14 GMT

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.