BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
rajdeep
Pyrite | Level 9

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mkeintz
PROC Star
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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------

View solution in original post

11 REPLIES 11
novinosrin
Tourmaline | Level 20
 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;
novinosrin
Tourmaline | Level 20
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;
rajdeep
Pyrite | Level 9

Hi 

 

 

novinosrin
Tourmaline | Level 20

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

Astounding
PROC Star

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.

rajdeep
Pyrite | Level 9

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

Astounding
PROC Star

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

mkeintz
PROC Star
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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
rajdeep
Pyrite | Level 9

Hi 

 

 

 

 

 

mkeintz
PROC Star

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
rajdeep
Pyrite | Level 9

Hi  

 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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