BookmarkSubscribeRSS Feed
help09
Fluorite | Level 6

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
5 REPLIES 5
novinosrin
Tourmaline | Level 20
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;

help09
Fluorite | Level 6

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

MarkWik
Quartz | Level 8

@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

ballardw
Super User

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.

 

help09
Fluorite | Level 6

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

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
  • 5 replies
  • 2470 views
  • 1 like
  • 4 in conversation