topic Re: Row by row comparison and update previous row text to current row at few instances in SAS Programming

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

help09 — Wed, 25 Apr 2018 19:00:25 GMT

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

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

novinosrin — Wed, 25 Apr 2018 19:08:01 GMT

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;

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

MarkWik — Wed, 25 Apr 2018 22:40:56 GMT

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

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

ballardw — Wed, 25 Apr 2018 23:15:47 GMT

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.

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

help09 — Tue, 15 May 2018 02:29:07 GMT

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

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

help09 — Tue, 15 May 2018 03:16:44 GMT

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