DATA Step, Macro, Functions and more

Row by row comparison and update previous row text to current row at few instances

Reply
Contributor krc
Contributor
Posts: 21

Row by row comparison and update previous row text to current row at few instances

[ Edited ]

Thanks for your support in advance

 

Below is the input

INPUT
sno txt sq
1 a 1
1 b 2
1 u 3
1 u 4
1 u 5
1 c 6
2 a 1
2 b 2
3 a 1
3 b 2
3 u 3
3 c 4
3 u 5
3 u 6

 

Need output as shown below variable txt with 'u' observation with previous value.& current value i.e 'u' as shown in BOLD.

 

OUTPUT
sno txt sq
1 a 1
1 b 2
1 bu 3
1 bu 4
1 bu 5
1 c 6
2 a 1
2 b 2
3 a 1
3 b 2
3 bu 3
3 c 4
3 cu 5
3 cu 6
PROC Star
Posts: 1,351

Re: Row by row comparison and update previous row text to current row at few instances

data have;
input sno	txt : $8.	sq;
datalines;
1	a	1
1	b	2
1	u	3
1	u	4
1	u	5
1	c	6
2	a	1
2	b	2
3	a	1
3	b	2
3	u	3
3	c	4
3	u	5
3	u	6
;

data want;
set have;
by sno;
length k $8;
if first.sno then call missing(k);
retain k;
if txt='u' then txt=cats(k,txt);
else k=txt;
drop k;
run;

Contributor krc
Contributor
Posts: 21

Re: Row by row comparison and update previous row text to current row at few instances

Posted in reply to novinosrin

Thank you

now i have couple of more records with below instance sno 4,5

INPUT

sno txt sq
1 a 1
1 b 2
1 u 3
1 u 4
1 u 5
1 c 6
2 a 1
2 b 2
3 a 1
3 b 2
3 u 3
3 c 4
3 u 5
3 u 6
4 u 1
4 b 2
5 a 1
5 u 2
5 b 3

Need output as shown below variable txt with 'u' observation for sno 4 and 5 as well

OUTPUT

sno txt sq
1 a 1
1 b 2
1 bu 3
1 bu 4
1 bu 5
1 c 6
2 a 1
2 b 2
3 a 1
3 b 2
3 bu 3
3 c 4
3 cu 5
3 cu 6
4 bu 1
4 b 2
5 a 1
5 bu 2
5 b 3

Frequent Contributor
Posts: 99

Re: Row by row comparison and update previous row text to current row at few instances

@krc The solution is straight forward and simple with  retain, concatenate and reset logic

 

If the question is answered, you could mark and close the thread

Super User
Posts: 13,084

Re: Row by row comparison and update previous row text to current row at few instances

If the length of your current TXT field is 1 (proc contents or other method methods examining the table to tell) then your solution will require setting a length for the variable to hold the longest expected value.

This would look something like:

data want;
  length txt $ 2;
  set have;
/* other code*/
run;

Note that if the length statement is after the Set you'll get a warning that you can't change existing variable length.

 

Contributor krc
Contributor
Posts: 21

Re: Row by row comparison and update previous row text to current row at few instances

Thank you

 

now i have couple of more records with below instance sno 4,5

INPUT

sno txt sq
1 a 1
1 b 2
1 u 3
1 u 4
1 u 5
1 c 6
2 a 1
2 b 2
3 a 1
3 b 2
3 u 3
3 c 4
3 u 5
3 u 6
4 u 1
4 b 2
5 a 1
5 u 2
5 b 3

 

Need output as shown below variable txt with 'u' observation for sno 4 and 5 as well

 

OUTPUT

sno txt sq
1 a 1
1 b 2
1 bu 3
1 bu 4
1 bu 5
1 c 6
2 a 1
2 b 2
3 a 1
3 b 2
3 bu 3
3 c 4
3 cu 5
3 cu 6
4 bu 1
4 b 2
5 a 1
5 bu 2
5 b 3

Ask a Question
Discussion stats
  • 5 replies
  • 149 views
  • 1 like
  • 4 in conversation